More FOR UPDATE/FOR SHARE problems

This post is a follow-up of an off-list discussion with Nathan Boley.
All references to FOR UPDATE apply to FOR SHARE as well.

create table a(i int, j int);
insert into a values(1, 10);
insert into a values(2, 10);
insert into a values(3, 10);
insert into a values(4, 20);
insert into a values(5, 20);
insert into a values(6, 20);

Session 1: BEGIN; UPDATE a SET j = (j - 10) WHERE i = 3 OR i = 4;

Session 2: SELECT * FROM a WHERE j = 10 FOR UPDATE; -- blocks

Session 1: COMMIT;

Session 2 (results):  i | j   ---+----  1 | 10  2 | 10 (2 rows)

There you see a snapshot of the table that never existed. Either the
snapshot was taken before the UPDATE, in which case i=3 should be
included, or it was taken after the UPDATE, in which case i=4 should be
included. So atomicity is broken for WHERE.

So, FOR UPDATE produces known incorrect results for:* WHERE* ORDER BY:
http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php*LIMIT* SAVEPOINT/ROLLBACK TO
 

And I expect we'll find more, as well.

It's not simply that FOR UPDATE works strangely in a couple isolated
edge cases, as the docs imply. It works contrary to the basic
assumptions that people familiar with PostgreSQL rely on. Furthermore,
the people using FOR UPDATE are likely to be the people who care about
these edge cases.

I think that FOR UPDATE deserves a jarring disclaimer in the docs if we
maintain the current behavior. Something along the lines of "this does
not follow normal transactional semantics and will produce incorrect
results". Existing users may find current FOR UPDATE behavior useful to
avoid full-table locks, but they should be properly warned.

If there is a fix, the only thing that I can imagine working (aside from
a full table lock) would be to iteratively acquire new snapshots and
re-run the query until no concurrent transaction interferes.

Regards,Jeff Davis