Re: Re: BUG #18750: Inappropriate update when it is blocked in RC

I'm so sorry that I forgot to provide you with the value of initial table t. The table is created as follows:
/* init */ CREATE TABLE t(a INT, b INT);
/* init */ INSERT INTO t VALUES (null, 1), (1, 1);

2024-12-25 01:24:07 "Greg Sabino Mullane" <htamfids(at)gmail(dot)com> 写道：
It will re-evaluate the rows it is already slated to update, it is not going to re-run the whole query and get a fresh list of rows to update. Here's two other examples. Uppercase is messages returned to psql from the server.

create table t (id int);
insert into t values (1),(2);

/* tx1 */ begin; -- isolation level does not matter
/* tx1 */ select * from t for update; -- lightweight lock on all rows

/* tx2 */ begin transaction isolation level read committed;
/* tx2 */ update t set id=999 where id = 2; -- hangs, waiting for the lock
/* tx1 */ update t set id = 4; -- the where clause is now no longer true for that row
/* tx1 */ UPDATE 2 -- server says we have updated two rows
/* tx1 */ commit;

/* tx2 */ UPDATE 0 -- server says that row no longer meets the WHERE clause, so no update
/* tx2 */ update t set id=999 where id = 2;
/* tx2 */ UPDATE 0 -- everything is a 4
/* tx2 */ update t set id=999 where id = 4;
/* tx2 */ UPDATE 2

truncate table t;
insert into t values (1),(2);

/* tx1 */ begin; select * from t for update;
/* tx2 */ begin transaction isolation level read committed;
/* tx2 */ update t set id=999 where id = 2; -- hangs, waiting for the lock
/* tx1 */ update t set id = 2; -- now have two rows that match the where clause
/* tx1 */ UPDATE 2 -- server says we have updated two rows
/* tx1 */ commit;

/* tx2 */ UPDATE 1 -- server verifies our original row is still valid, but does not update the "new" 2
/* tx2 */ update t set id=999 where id = 2; -- fresh look at all the rows
/* tx2 */ UPDATE 1 -- we have updated the "new" 2
/* tx2 */ update t set id=999 where id = 2;
/* tx2 */ UPDATE 0 -- nothing left

If you set the second transaction to "repeatable read", you will find that the above scenario will result in a "could not serialize access due to concurrent update" error, which, in my opinion, is a more sane result. One of the many reasons I tend to avoid "read committed".

Cheers,
Greg