From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: radiodiversion@gmail.com
Subject: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
Date: Tue, 17 Aug 2021 22:32:42 +0000
Message-ID: <162923956240.1646.8016878483043252237@wrigleys.postgresql.org> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, radiodiversion@gmail.com
Subject: Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
In-Reply-To: <162923956240.1646.8016878483043252237@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox