Thread: SELECT FOR UPDATE....LIMIT ...broken

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

Darren Govoni <darren@ontrenet.com> writes:
> 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

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.

On Sun, 2009-01-11 at 12:12 -0500, Tom Lane wrote:
> Darren Govoni <darren@ontrenet.com> writes:
> > 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
>