SELECT FOR UPDATE....LIMIT ...broken

[Darren Govoni]

Hi,
I have a simple scenario that is producing incorrect results with
8.3,ubuntu.

I have queries attempting the same "select-for update limit 1". at the
same time. There are 2 rows in my test database.

The first will enter the transaction and only update ONE of the rows it
selected, because it uses "LIMIT 1" in the select for update.only 1 row
is returned.

The second naturally will block when entering the query until the first
commits its update. But when the second unblocks, it sees 0 rows, even
though there is 1 row left that satisfies its query.

If I re-run the second query, it finds the 1 row left. But not when
released from the UPDATE block.

This seems like a bug to me. Is it fixed in the current dev build? Or is
there something else I'm missing?

Darren

[Tom Lane]

Post by Darren Govoni
This seems like a bug to me. Is it fixed in the current dev build? Or is
there something else I'm missing?

I think you have not read the documentation about FOR UPDATE and LIMIT
on the SELECT reference page:
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE
Note the second CAUTION block in particular.

regards, tom lane

[Darren Govoni]

Well, I did, but I clicked the link to Chapter 13 before scrolling down
further to see the Caution section. Heheh.

Thanks for the heads up.

Post by Tom Lane

Post by Darren Govoni
This seems like a bug to me. Is it fixed in the current dev build? Or is
there something else I'm missing?

I think you have not read the documentation about FOR UPDATE and LIMIT
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE
Note the second CAUTION block in particular.
regards, tom lane