On Thu, Oct 27, 2011 at 1:51 PM, Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: > Simon Riggs <si...@2ndquadrant.com> wrote: >> On Thu, Oct 27, 2011 at 4:41 PM, Kevin Grittner >> <kevin.gritt...@wicourts.gov> 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. > > Hmm. At first reading I thought this was related to the > mixed-snapshot issue in READ COMMITTED, but now I'm not so sure. > Does anyone know which isolation levels are affected? Barring that, > can anyone point to an existing test which demonstrates the problem? > > If this can happen in snapshot isolation with just one reader and > one writer, I doubt that SSI helps with it. :-(
Simple test case: rhaas=# create table oops (a int); CREATE TABLE rhaas=# insert into oops values (1), (2), (3), (4); INSERT 0 4 rhaas=# begin; BEGIN rhaas=# update oops set a = 5 where a = 2; UPDATE 1 In another session: rhaas=# select * from oops order by 1 for update; <this blocks> Back to the first session: rhaas=# commit; COMMIT Second session now returns: a --- 1 5 3 4 (4 rows) But if you do the same thing at REPEATABLE READ, you get: ERROR: could not serialize access due to concurrent update STATEMENT: select * from oops order by 1 for update; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
