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 <[email protected]> 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">
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
