BUG #4925: "select ... for update" doesn't affect rows from sub-query

The following bug has been logged online:

Bug reference: 4925
Logged by: Steve Caligo
Email address: steve(dot)caligo(at)ctie(dot)etat(dot)lu
PostgreSQL version: 8.3.7 and 8.4.0
Operating system: Archlinux and Gentoo 8.3.7, Gentoo 8.4.0
Description: "select ... for update" doesn't affect rows from
sub-query
Details:

While trying to guarantee data consistency when doing concurrent processing,
I stumbled upon your cautions mentionned in your documentation (especially
"limit" ... "for update"):
http://www.postgresql.org/docs/8.4/static/sql-select.html

I tried working around this limitation and the statement on the same page
seemed promising to me:
"If FOR UPDATE or FOR SHARE is applied to a view or sub-query, it affects
all tables used in the view or sub-query."

But unfortunately the latter statement doesn't seem to be true and the
subquery isn't protected by row locks, as the following examples show.

1) INITIAL SITUATION

Create a simple table with some data. No constraints, no indexes, just the
bare minimum:

create table test (
id integer,
name varchar(10),
c integer
);

insert into test values
(1, 'test1', 0),
(2, 'test2', 0),
(3, 'test3', 0),
(4, 'test4', 0),
(5, 'test5', 0)
;

2) FIRST TRY, USING "UPDATE WHERE ID = ( SELECT )"
The goal is to have two clients set their unique ID to a single/different
row from the table. First, using "limit" in a slightly different way:

1=> begin transaction;
2=> begin transaction;

1=> update test set c = 1 where id = (
select id from test where c = 0 order by id limit 1
)
; -- updates row id=1
2=> update test set c = 2 where id = (
select id from test where c = 0 order by id limit 1
)
; -- forced to wait on lock

1=> commit; -- client #2 continues
1=> select * from test where id = 1;
id | name | c
----+-------+---
1 | test1 | 1
(1 row)

2=> commit;
2=> select * from test where id = 1;
id | name | c
----+-------+---
1 | test1 | 2
(1 row)

1=> select * from test where id = 1;
id | name | c
----+-------+---
1 | test1 | 2
(1 row)

Conclusion: didn't work.
Probably because the subquery is executed before the update and not affected
by row locking.

Expected behaviour: one client update one row to c=1 and the other client
updates a different row to c=2.

3) SECOND TRY, PROTECTING THE UPDATE BY AN ADDITIONAL "SELECT ... FOR
UPDATE" AND AVOIDING THE "LIMIT"

1=> begin transaction;
2=> begin transaction;
1=> select id from test where id = (
select min(id) from test where c = 0
) for update;
id
----
2
(1 row)

2=> select id from test where id = (
select min(id) from test where c = 0
) for update; -- forced to wait on lock

1=> update test set c = 1 where id = 2;
1=> commit; -- client #2 continues:

2=> -- client #2 outputs:
id
----
2
(1 row)

2=> select * from test where id = 2;
id | name | c
----+-------+---
2 | test2 | 1
(1 row)
2=> -- now this isn't what we initially asked for, let's just repeat the
query once more:

2=> select id from test where id = ( select min(id) from test where c = 0 )
for update;
id
----
3
(1 row)

Conclusion: didn't work.
The situation one ends up in is one that contradicts your above statement,
but also seemingly violates the "I" in ACID. In 3), client #2 is clearly
affected by the actions of client #1. While serialized transactions or full
table locks would avoid this race condition, it either requires large
changes in the application or impacts performance during contention.

Adding an additional "c = 0" to the main query of 3) of course suppresses
the row from the concurrent update, but it shouldn't have been returned with
a value of "c = 1" because the transaction #2 started prior to the update
statement of #1.