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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073