Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

I have documented the possible problem with LIMIT and FOR UPDATE. I
also remove the mention that FOR UPDATE can appear before LIMIT for
pre-7.3 compatibility.

Patch applied to CVS HEAD only.

---------------------------------------------------------------------------

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?
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
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