Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

Josh Berkus wrote:
> 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?

I am wondering if a documentation warning about the use of FOR UPDATE
and LIMIT is a good idea. If we can't be sure the LIMIT will return a
guaranteed number of rows, should we just disallow that combination? I
realize such a case is rare. Should we emit a warning when it happens?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073