Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

Tom, Neil,

> > Au contraire: every row that gets locked will be returned to the client.
> > The gripe at hand is that the number of such rows may be smaller than
> > the client wished, because the LIMIT step is applied before we do the
> > FOR UPDATE step

As I said, I think this can be taken care of with a doc patch. The truth is
that FOR UPDATE LIMIT is not really terribly useful (it will still block
outer queries to that table with the same LIMIT clause, so why not lock the
whole table?). I propose that I add this sentence to the Docs:

--------------
Please not that, since LIMIT is applied before FOR UPDATE, rows which
disappear from the target set while waiting for a lock may result in less
than LIMIT # of rows being returned. This can result in unintuitive
behavior, so FOR UPDATE and LIMIT should only be combined after significant
testing.
---------------

Here's a question, though, for my education: It's possible to query "Please
lock the first row which is not already locked" by including pg_locks,
pg_class and xmax in the query set. Tom warned that this could result in a
race condition. If the query-and-lock were a single statement, how would a
race condition result? How could I test for it?

--
Josh Berkus
Aglio Database Solutions
San Francisco