Read Committed anomaly in PostgreSQL working on inconsistent snapshot

[FranckPachot]

Hi, There's an anomaly in PostgreSQL that is not caught by those tests because it doesn't implement statement restart like Oracle or YugabyteDB (I didn't test other MVCC). Here is an example where T1 changes value from 20 to 10 and from 10 to 20.
Then T2 updates or deletes for value=10 should modify one row. As it has to wait for T1 to commit, it should re-read a snapshot then. But PostgreSQL doesn't implement that and re-reads only the next rows, keeping the changes done on a different read time, then working on an inconsistent snapshot.

The following example on your tables uses value=30-value to change 10 to 20 and 20 to 10

create table test (id int primary key, value int);
insert into test (id, value) values (1, 10), (2, 20);

Postgres "read committed" -> inconsistent DML

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value=30-value; -- T1
update test set value = 12 where id = 1; -- T2, BLOCKS
delete from test where value=10 -- T1
commit; -- T1. This unblocks T2
commit; -- T2
select * from test; -- either. Shows 1 => 20, 2 => 10

YugabyteDB "read committed" -> consistent DML

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value=30-value; -- T1
update test set value = 12 where id = 1; -- T2, BLOCKS
delete from test where value=10 -- T1
commit; -- T1. This unblocks T2
commit; -- T2
select * from test; -- either. Shows 1 => 20