public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khan, Tanzeel <[email protected]>
To: [email protected] <[email protected]>
Subject: SELECT FOR UDPATE behavior inside joins
Date: Mon, 29 Dec 2025 09:15:58 +0000
Message-ID: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com> (raw)
Hi,
I am trying to understand the SELECT FOR UPDATE behavior when it is not returning rows back to client.
postgres=> CREATE TABLE t (col1 INT, col2 INT);
postgres=> INSERT INTO t VALUES (1, 1);
S1: BEGIN; UPDATE t SET col2 = col2 + 1 WHERE col1 = 1;
S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 = 1 FOR UPDATE) UPDATE t SET col2 = t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 = t_self_join.col2);
S1: COMMIT;
S2: zero rows updated
Why does session 2 update zero rows ? Shouldn’t the SELECT FOR UPDATE and UPDATE read the new version of the row as per
> If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED
Does this mean the new version for row is only returned when the SELECT FOR SHARE is returning rows back to client ?
------
Thanks,
Tanzeel
view thread (4+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: SELECT FOR UDPATE behavior inside joins
In-Reply-To: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox