Odd behaviour of SELECT ... ORDER BY ... FOR UPDATE

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Odd behaviour of SELECT ... ORDER BY ... FOR UPDATE
Date: 2015-07-02 07:15:30
Message-ID: 5594E511.3070007@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

While working on the foreign-join-pushdown issue, I noticed that in READ
COMMITTED isolation level it's possible that the result of SELECT ...
ORDER BY ... FOR UPDATE is not sorted correctly due to concurrent
updates that replaced the sort key columns with new values as shown in
the below example. That seems odd to me. So, I'd like to propose
raising an error rather than returning a possibly-incorrect result for
cases where the sorted tuples to be locked were modified by concurrent
updates. Patch attached. Is it OK to add this to the current CF?

Create an environment:

postgres=# create table test (a int);
CREATE TABLE
postgres=# insert into test values (1);
INSERT 0 1
postgres=# insert into test values (2);
INSERT 0 1

Run an example:

[Terminal 1]
postgres=# begin;
BEGIN
postgres=# update test set a = 3 where a = 1;
UPDATE 1

[Terminal 2]
postgres=# select * from test order by a for update;

[Terminal 1]
postgres=# commit;
COMMIT

[Terminal 2]
(The following result will be shown after the commit in Terminal 1.
Note that the output ordering is not correct.)
a
---
3
2
(2 rows)

Best regards,
Etsuro Fujita

Attachment Content-Type Size
select-orderby-forupdate-v1.patch text/x-patch 4.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2015-07-02 07:16:30 Re: Support for N synchronous standby servers - take 2
Previous Message Amit Langote 2015-07-02 07:12:36 Re: Support for N synchronous standby servers - take 2