From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Khan, Tanzeel" <tzlkhan@amazon.com>
cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: SELECT FOR UDPATE behavior inside joins
In-reply-to: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
References: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
Comments: In-reply-to "Khan, Tanzeel" <tzlkhan@amazon.com>
	message dated "Mon, 29 Dec 2025 09:15:58 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3914048.1767195192.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 31 Dec 2025 10:33:12 -0500
Message-ID: <3914049.1767195192@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3914049.1767195192%40sss.pgh.pa.us>
Precedence: bulk

"Khan, Tanzeel" <tzlkhan@amazon.com> writes:
> I am trying to understand the SELECT FOR UPDATE behavior when it is not =
returning rows back to client.

> postgres=3D> CREATE TABLE t (col1 INT, col2 INT);
> postgres=3D> INSERT INTO t VALUES (1, 1);

> S1: BEGIN; UPDATE t SET col2 =3D col2 + 1 WHERE col1 =3D 1;
> S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 =3D 1 FOR UPDATE) UPD=
ATE t SET col2 =3D t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 =3D t_=
self_join.col2);
> S1: COMMIT;
> S2: zero rows updated

> Why does session 2 update zero rows ?

Since the CTE has FOR UPDATE, it blocks and returns the updated-by-S1
version of the row.  But the outer query initially reads the old
version of the row, so the join condition fails, and we never get
to the lock-row-and-recheck behavior of UPDATE.

I am not sure what you are hoping to accomplish with that self-join.
I suppose this is an oversimplified example, but it's too
oversimplified for anyone to see why you'd want to do it like that.

			regards, tom lane