public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khan, Tanzeel <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: SELECT FOR UDPATE behavior inside joins
Date: Thu, 1 Jan 2026 08:40:40 +0000
Message-ID: <CH5PR18MB927659CE30CC405704D6D81E6DCDBAA@CH5PR18MB927659.namprd18.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
<[email protected]>
> 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 see, thanks.
> 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.
I was original trying to build a generic way for fetching old rows in
UPDATE RETURNING clause for pre-18 versions but the self join does
not work well with concurrent updates either resulting in lost updates
or not returning the concurrently updated value in RETURNING.
-----------
Thanks,
Tanzeel
view thread (4+ messages)
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: <CH5PR18MB927659CE30CC405704D6D81E6DCDBAA@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