Tom Lane <tgl@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