Subject: Re: SELECT FOR UDPATE behavior inside joins
Thread-Topic: SELECT FOR UDPATE behavior inside joins
From: "Khan, Tanzeel" <tzlkhan@amazon.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Thread-Index: AQHceKFQG1f4JGngWEKdz2KQb5dGT7U75EkAgAEA/d8=
Date: Thu, 1 Jan 2026 08:40:40 +0000
Message-ID: <CH5PR18MB927659CE30CC405704D6D81E6DCDBAA@CH5PR18MB927659.namprd18.prod.outlook.com>
References: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
 <3914049.1767195192@sss.pgh.pa.us>
In-Reply-To: <3914049.1767195192@sss.pgh.pa.us>
Accept-Language: en-IN, en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 2
x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?hTbjHLlVsT8VjmY94d9s2gK1kCPVXZhLRB7kuDVadKGxCH6xQ5/0tZ2RGrAn?=
 =?us-ascii?Q?lWM8C+t57EWljNCsba78VuXfeJ2QAyCYkJs4GWHbI1GWHXW6Vi/JlMZ5tol8?=
 =?us-ascii?Q?FJZ39PWD0QV6uKtJBc52yVioX1M7QxiwKwZB9j95FdqeXdQG3XvGazsTRIcv?=
 =?us-ascii?Q?HRb6foDugZ3gwpm+/w6msnCqcoUyahLcl+ORsg2Q2dcqLBQCT0LtJoiWLaLk?=
 =?us-ascii?Q?SpYZolweonjdZJZfvRcMN+qxLHdSDlUEGR/5T5hVxIy6+urHd/XL2BxRlxP0?=
 =?us-ascii?Q?womLvxJb8T0K7kQpkzaNTmSsTgI8cyo9QJBEwPV76KqE8YvBpzwTi6W34q2Y?=
 =?us-ascii?Q?WnK8LZgEQuqCyl6BD0jzd+7tBcxTOcUq4+UI2OATAW7w/tHL8dK7iymOnevG?=
 =?us-ascii?Q?lcgWYYKmSZiEAoFLyosKJO7tjR7DNxHbWwBgwwocV9pphTVPcMw1Thng7cJB?=
 =?us-ascii?Q?ooM7xlU4B6XBZgIIz7eaoAZRiuG70Z9fxBvXbZtvSXRFJqbYnGN4YzmsPmmx?=
 =?us-ascii?Q?cL5K389RpDUWky8CVtIF10GajIxr6OatpO4DytPxQ+kYdshh1iizGkiHiNWS?=
 =?us-ascii?Q?ssyMhcwq1JixpRBdQwztBNEhZWi00XKotnG3kUj4n3FXD5NA26MPBfAMQnPE?=
 =?us-ascii?Q?NzU1ExrAd8vLl7Dti6ydWNYqTwbGiq5CJ0FmBPCp/lDgfcTMIGZeuHd4DaOb?=
 =?us-ascii?Q?xDl3mXhymDNKdUp0z6wKaSgaono3NKAGWLKU5LUHRlbhcTrshtuX6bSR3lzm?=
 =?us-ascii?Q?Ng/SpSUaNFDJydzTg+LU6HqHmZl7ThiVpSTIOwBgtetiHi+jca7hj5T3XR28?=
 =?us-ascii?Q?kPJLofNmg8ktcdUYER7KtQ38VBCBU7CTiKD+uHUTPRU1yTNP5FARTNSieZim?=
 =?us-ascii?Q?wjtl7Ko+mqa1IzghlOPxRF2TYr8v3q8gFXaoR3vNuZ7XOo8gEkZT8KK6soee?=
 =?us-ascii?Q?qgJvjOHeU7649oNrCmEXLBiZcjTmBVSxihnTzDFAh/m4MsbWNn+G0XgA6JEi?=
 =?us-ascii?Q?VYoiKmM3BuyEZTn5w0XW15VxjjYMAcV7mShQL8CahGugjj3vXaIbuoe9zL61?=
 =?us-ascii?Q?QZeYacKFJvwmAfBoXDNGzEwIu/OWnq8ptEv0IHtwsokKz4hts0uWIZPWVFvV?=
 =?us-ascii?Q?egcNvg84lI2H59eb7Q8H/lDfX8LEpwfNaHpc8D50Ux9n+LIHQPK2bCQDFH00?=
 =?us-ascii?Q?ahgFzMsortuohR6lbsgrfDJvD11Rp2VETy4e5i37ZjqegAG2MOjkb0peO0mP?=
 =?us-ascii?Q?tKlknqL9ZN/+m8pOVp1GUlOMA/ajlR5O6FMuz9ip50I9KXZvF1Wk+PQBsdER?=
 =?us-ascii?Q?qtC2853VNtRCAxN7u1uqBkCXIivt/SX2vkFrhYH5l/raP6QKSM2YGF5hR5Ob?=
 =?us-ascii?Q?gX6O/tC4kzYZoCRGTjV6CUDVxPSWrfpD/czS1u9RWP71Z+QP9173HpeOrVxT?=
 =?us-ascii?Q?mlm4IwgM/6YZ19ttTegFhTUlw869KLftSgUiJhFQc61zIVkyUT3UZJk0iobY?=
 =?us-ascii?Q?lHnhzq9JrGD2UPymZG3pmlAp0trPIgsDorb5+/AtZGxlXjc5b52ajtb/74ic?=
 =?us-ascii?Q?p4idaq4PuBFh2XZ6FpvfJtz7OCBeLr6mGQK9XpWo77xQGRC0puzhVWJowuPA?=
 =?us-ascii?Q?xltJeQWAqAdMYBpmeRPTFH6m7SZybnQzpKiUYrG+f/LnFUalagUUJkFPh0bt?=
 =?us-ascii?Q?QZXbeZYRsdEcVF2BLb/+gzwT6dQFvy/PW9cbmhNlgzSqST2tCSVoUM1wk1m7?=
 =?us-ascii?Q?E61rJ1iRXErJdmp7+dmAgd/PBoG+nk+Zgcy/+rM34M5aYxGgSrwHui75IhQ7?=
x-ms-exchange-antispam-messagedata-1: JHnrjiDuinK3N2EDKif/La/DY0XBaCLkIJg=
Content-Type: multipart/alternative;
	boundary="_000_CH5PR18MB927659CE30CC405704D6D81E6DCDBAACH5PR18MB927659_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: CH5PR18MB927659.namprd18.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 4f4de3fb-64c8-40a0-251c-08de49117208
X-MS-Exchange-CrossTenant-originalarrivaltime: 01 Jan 2026 08:40:40.9097
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 5280104a-472d-4538-9ccf-1e1d0efe8b1b
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: J3nvcEZRCOAntMPzCbmkKTHEMoiq75mUlDsO3DCsw9dgL8CoPX8d7UmUgVkooYq2dzjsKiT06Og3EdumpgDldA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: LV8PR18MB5882
X-OriginatorOrg: amazon.com
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/CH5PR18MB927659CE30CC405704D6D81E6DCDBAA%40CH5PR18MB927659.namprd18.prod.outlook.com>
Precedence: bulk

--_000_CH5PR18MB927659CE30CC405704D6D81E6DCDBAACH5PR18MB927659_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

> 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

--_000_CH5PR18MB927659CE30CC405704D6D81E6DCDBAACH5PR18MB927659_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
</head>
<body>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
&gt; Since the CTE has FOR UPDATE, it blocks and returns the updated-by-S1<=
br>
&gt; version of the row.&nbsp; But the outer query initially reads the old<=
br>
&gt; version of the row, so the join condition fails, and we never get<br>
&gt; to the lock-row-and-recheck behavior of UPDATE.<br>
<br>
I see, thanks.<br>
<br>
&gt; I am not sure what you are hoping to accomplish with that self-join.<b=
r>
&gt; I suppose this is an oversimplified example, but it's too<br>
&gt; oversimplified for anyone to see why you'd want to do it like that.<br=
>
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
I was original trying to build a generic way for fetching old rows in</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
UPDATE RETURNING clause for pre-18 versions but the self join does</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
not work well with concurrent updates either resulting in lost updates</div=
>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
or not returning the concurrently updated value in RETURNING.</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
-----------</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
Thanks,</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 11pt; color: rgb(0, 0, 0);">
Tanzeel</div>
</body>
</html>

--_000_CH5PR18MB927659CE30CC405704D6D81E6DCDBAACH5PR18MB927659_--