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
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.83
diff -c -c -r1.83 select.sgml
*** doc/src/sgml/ref/select.sgml 8 Apr 2005 00:59:58 -0000 1.83
--- doc/src/sgml/ref/select.sgml 22 Apr 2005 04:15:06 -0000
***************
*** 830,840 ****
</para>
<para>
! <literal>FOR UPDATE</literal> may appear before
! <literal>LIMIT</literal> for compatibility with
! <productname>PostgreSQL</productname> versions before 7.3. It
! effectively executes after <literal>LIMIT</literal>, however, and
! so that is the recommended place to write it.
</para>
</refsect2>
</refsect1>
--- 830,842 ----
</para>
<para>
! It is possible for a <command>SELECT</> command using both
! <literal>LIMIT</literal> and <literal>FOR UPDATE</literal>
! clauses to return fewer rows than specified by <literal>LIMIT</literal>.
! This is because <literal>LIMIT</> selects a number of rows,
! but might then block requesting a <literal>FOR UPDATE</literal> lock.
! Once the <literal>SELECT</> unblocks, the query qualifiation might not
! be met and the row not be returned by <literal>SELECT</>.
</para>
</refsect2>
</refsect1>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
