Re: [BUGS] Status of issue 4593 - Mailing list pgsql-hackers

Jeff Davis wrote:
> On Mon, 2009-01-12 at 13:35 -0500, Tom Lane wrote:
> > I think the behavior Lee is expecting is only implementable with a
> > full-table write lock, which is exactly what FOR UPDATE is designed
> > to avoid.  There are certain properties you don't get with a partial
> > lock, and in the end I think we can't do much except document them.
> > We have LOCK TABLE for those who need the other behavior.
> >
>
> Lee said specifically that he's not using LIMIT, and there's already a
> pretty visible warning in the docs for using LIMIT with FOR UPDATE.
> Also, using LIMIT + FOR UPDATE has a dangerous-looking quality to it (at
> least to me) that would cause me to do a little more investigation
> before relying on its behavior.
>
> I'm not pushing for FOR UPDATE + ORDER BY to be blocked outright, but I
> think it's strange enough that it should be considered some kind of
> defect worse than the cases involving LIMIT that you mention.

I have added the attached documentation mention to CVS HEAD and 8.3.X.
If people want a TODO entry or to issue a WARNING message on use, please
let me know.  This does seem similar to the FOR UPDATE / LIMIT issue so
I handled it similarly.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.117
diff -c -c -r1.117 select.sgml
*** doc/src/sgml/ref/select.sgml    12 Jan 2009 14:06:20 -0000    1.117
--- doc/src/sgml/ref/select.sgml    22 Jan 2009 22:50:20 -0000
***************
*** 1162,1177 ****
    <caution>
     <para>
      It is possible for a <command>SELECT</> command using both
!     <literal>LIMIT</literal> and  <literal>FOR UPDATE/SHARE</literal>
      clauses to return fewer rows than specified by <literal>LIMIT</literal>.
      This is because <literal>LIMIT</> is applied first.  The command
      selects the specified number of rows,
!     but might then block trying to obtain lock on one or more of them.
      Once the <literal>SELECT</> unblocks, the row might have been deleted
      or updated so that it does not meet the query <literal>WHERE</> condition
      anymore, in which case it will not be returned.
     </para>
    </caution>
    </refsect2>

    <refsect2 id="SQL-TABLE">
--- 1162,1192 ----
    <caution>
     <para>
      It is possible for a <command>SELECT</> command using both
!     <literal>LIMIT</literal> and <literal>FOR UPDATE/SHARE</literal>
      clauses to return fewer rows than specified by <literal>LIMIT</literal>.
      This is because <literal>LIMIT</> is applied first.  The command
      selects the specified number of rows,
!     but might then block trying to obtain a lock on one or more of them.
      Once the <literal>SELECT</> unblocks, the row might have been deleted
      or updated so that it does not meet the query <literal>WHERE</> condition
      anymore, in which case it will not be returned.
     </para>
    </caution>
+
+   <caution>
+    <para>
+     Similarly, it is possible for a <command>SELECT</> command
+     using <literal>ORDER BY</literal> and <literal>FOR
+     UPDATE/SHARE</literal> to return rows out of order.  This is
+     because <literal>ORDER BY</> is applied first.  The command
+     orders the result, but might then block trying to obtain a lock
+     on one or more of the rows.  Once the <literal>SELECT</>
+     unblocks, one of the ordered columns might have been modified
+     and be returned out of order.  A workaround is to perform
+     <command>SELECT ... FOR UPDATE/SHARE</> and then <command>SELECT
+     ... ORDER BY</>.
+    </para>
+   </caution>
    </refsect2>

    <refsect2 id="SQL-TABLE">