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