Subject: SELECT FOR UDPATE behavior inside joins

From: "Khan, Tanzeel" <[email-masked]>
Date: Mon, 29 Dec 2025 09:15:58 +0000

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 31 Dec 2025 10:33:12 -0500

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 31 Dec 2025 07:35:38 -0800

On 12/29/25 01:15, Khan, Tanzeel wrote:
> 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 <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 ?

No I think  you missed the sentence before the one you you show above:

"The search condition of the command (the WHERE clause) is re-evaluated 
to see if the updated version of the row still matches the search 
condition. If so, the second updater proceeds with its operation using 
the updated version of the row."

So:

  WHERE (t.col2 = t_self_join.col2)

The S1 UPDATE changed the value of col2 to something different then what 
is is found by WITH cte AS (SELECT * FROM t WHERE col1 = 1 FOR UPDATE).

-- 
Adrian Klaver
[email-masked]

Subject: Re: libpq simple SELECT
From: Laurenz Albe <[email-masked]>
To: Igor Korot <[email-masked]>
Cc: "pgsql-generallists.postgresql.org" <[email-masked]>
Date: Wed, 17 Dec 2025 21:22:32 +0100
Archived-At: <https://www.postgresql.org/message-id/3fd9c2859c323d6f894ccf660764f24dc3423181.camel%40cybertec.at>

On Wed, 2025-12-17 at 09:42 -0800, Igor Korot wrote:
> On Tue, Dec 16, 2025 at 11:34=E2=80=AFPM Laurenz Albe <laurenz.albe@cyber=
tec.at> wrote:
> > On Tue, 2025-12-16 at 21:49 -0800, Igor Korot wrote:
> > > I just tried the following code:
> > >=20
> > > [code]
> > > =C2=A0 =C2=A0 =C2=A0std::wstring query1 =3D L"SELECT t.table_catalog =
AS catalog,
> > > t.table_schema AS schema, t.table_name AS table, u.usename AS owner,
> > > c.oid AS table_id FROM information_schema.tables t,
> > > pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name =3D
> > > c.relname AND c.relowner =3D usesysid AND (t.table_type =3D 'BASE TAB=
LE'
> > > OR t.table_type =3D 'VIEW' OR t.table_type =3D 'LOCAL TEMPORARY') ORD=
ER BY
> > > table_name;";
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0res =3D PQexec( m_db, m_pimpl->m_my=
conv.to_bytes( query1.c_str()
> > > ).c_str() );
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for( int i =3D 0; i < PQntuples( r=
es ); i++ )
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 {
> > > some code handling the results
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 }
> > > [/code]
> > >=20
> > > The loop is executed exactly 1 time.
> > >=20
> > > My question is - according to the comment above I don't have to use c=
ursor,
> > > but it looks like it's unavoidable?
> >=20
> > I see no cursor in your example...
>=20
> That is my question.
>=20
> The code shows cursor but the comment indicate that the cursor is not req=
uired.

I am still confused.

You quoted a comment from a sample program in the documentation, and that
example is actually using a cursor:

    res =3D PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_data=
base");

But your code does not use a cursor.  So why should the comment apply to it=
?

Yours,
Laurenz Albe

---

From: "Khan, Tanzeel" <[email-masked]>
Date: Thu, 1 Jan 2026 08:40:40 +0000

> to the lock-row-and-recheck behavior of UPDATE.

I see, thanks.

> 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