Tom Lane <t...@sss.pgh.pa.us> wrote: > [a transaction] might have also changed some other row so that it > now *does* satisfy WHERE, but we won't ever find that other row > because in the query snapshot it doesn't pass the WHERE. OK; got it. No way to fix that, really, without getting a fresh snapshot and re-starting the command, is there? I take it from your earlier posts that wouldn't be pretty. On the bright side, to be taken as showing an inconsistent state, the transaction on which we block has to both move one or more rows into the matching set as well as moving one or more rows out. Another example of the phenomenon: connection1: ============ test=# create table t (name text not null primary key, is_it boolean not null); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE test=# insert into t values ('huey', true), ('dewey', false), ('louie', false); INSERT 0 3 test=# start transaction isolation level read committed; START TRANSACTION test=# update t set is_it = not is_it where name in ('huey', 'dewey'); UPDATE 2 connection2: ============ test=# start transaction isolation level read committed; START TRANSACTION test=# select * from t where is_it for update; [blocks] connection1: ============ test=# commit; COMMIT connection2: ============ name | is_it ------+------- (0 rows) test=# select * from t where is_it for update; name | is_it -------+------- dewey | t (1 row) So this particular issue means that rows affected will be the intersection of rows matching the WHERE clause before and after the conflicting concurrent transaction(s) commit. The join/subquery issue means that all values used would be based on the snapshot at the start of the statement except that values from rows updated by concurrent transactions on which we blocked would be based on the updated rows. Any other issues? -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers