public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Khan, Tanzeel <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: SELECT FOR UDPATE behavior inside joins
Date: Wed, 31 Dec 2025 10:33:12 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
References: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>

"Khan, Tanzeel" <[email protected]> writes:
> 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 ?

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

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], [email protected]
  Subject: Re: SELECT FOR UDPATE behavior inside joins
  In-Reply-To: <[email protected]>

* 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