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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
