Simon Riggs <[email protected]> wrote:
> On Thu, Oct 27, 2011 at 4:41 PM, Kevin Grittner
> <[email protected]> wrote:
>> On the docs page for the SELECT statement, there is a caution
>> which starts with:
>>
>> | It is possible for a SELECT command using ORDER BY and FOR
>> | UPDATE/SHARE to return rows out of order. This is because ORDER
>> | BY is applied first.
>>
>> Is this risk limited to queries running in READ COMMITTED
>> transactions? If so, I think that should be mentioned in the
>> caution.
>
> I think it should say that if this occurs with SERIALIZED
> transactions it will result in a serialisation error.
>
> Just to say there is no effect in serializable mode wouldn't be
> helpful.
OK, doc patch attached.
-Kevin
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 1281,1287 **** ROLLBACK TO s;
<caution>
<para>
! 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 sorts the result, but might then block trying to obtain a lock
--- 1281,1288 ----
<caution>
<para>
! It is possible for a <command>SELECT</> command running at the
<literal>READ
! COMMITTED</literal> transaction isolation level and 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 sorts the result, but might then block trying to obtain a lock
***************
*** 1302,1307 **** SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY
column1;
--- 1303,1315 ----
only if concurrent updates of the ordering columns are expected and a
strictly sorted result is required.
</para>
+
+ <para>
+ At the <literal>REPEATABLE READ</literal> or
<literal>SERIALIZABLE</literal>
+ transaction isolation level this would cause a serialization failure (with
+ a <literal>SQLSTATE</literal> of <literal>'40001'</literal>), so there is
+ no possibility of receiving rows out of order under these isolation
levels.
+ </para>
</caution>
</refsect2>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
