The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/transaction-iso.html
Description:
hello!
documentation for "read committed" says that:
"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ...
...If the first updater commits, the second updater will ignore the row if
the first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row. The search condition of the
command (the WHERE clause) is re-evaluated to see if the updated version of
the row still matches the search condition."
but this behaviour is not reproduced for UPDATE .. WHERE ..
update from second session don't takes into account updated data in row
committed by first session.
example:
session1:
begin;
create table t ( x numeric, y numeric );
insert into t values ( -1, 11 );
insert into t values ( 1, 12 );
commit; -- so table is now visible for another transactions
begin;
update t set x=-x, y=y+100; -- here rows is locked by session1 until
commit\rollback
session 2
begin;
update t set y=y+1000 where x<0; -- here session2 start waiting for
session1 commit\rollback
session1
commit;
session2
-- successfully completed. NO ROWS AFFECTED!
-- this means that update from session2 works definitely not like
described in documentation ("The search condition of the command (the WHERE
clause) is re-evaluated")
I would like to see in documentation description of REAL behaviour of
postgres for "second updater".
Or, maybe, it's a bug.. but it's a principal part of isolation mechanism, so
i think that it's just a documentation issue.
i take example from here:
https://franckpachot.medium.com/read-committed-anomalies-in-postgresql-f0d80330a0e0
it's reproduced on PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1)
many thanks!
