Feedback-ID: i76984098:Fastmail
Message-ID: <3fbea48a-54e9-4af2-880d-35707cc56d72@aklaver.com>
Date: Wed, 31 Dec 2025 07:35:38 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SELECT FOR UDPATE behavior inside joins
To: "Khan, Tanzeel" <tzlkhan@amazon.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/3fbea48a-54e9-4af2-880d-35707cc56d72%40aklaver.com>
Precedence: bulk

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

> 
> ------
> Thanks,
> Tanzeel


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vVy2h-00A9P9-0V
	for pgsql-general@arkaria.postgresql.org;
	Wed, 17 Dec 2025 20:22:40 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vVy2g-00GLAS-04
	for pgsql-general@arkaria.postgresql.org;
	Wed, 17 Dec 2025 20:22:38 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1vVy2f-00GLAJ-28
	for pgsql-general@lists.postgresql.org;
	Wed, 17 Dec 2025 20:22:38 +0000
Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343])
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
	(Exim 4.96)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1vVy2d-001IAs-1q
	for pgsql-general@lists.postgresql.org;
	Wed, 17 Dec 2025 20:22:37 +0000
Received: by mail-wm1-x343.google.com with SMTP id 5b1f17b1804b1-4779aa4f928so73076525e9.1
        for <pgsql-general@lists.postgresql.org>; Wed, 17 Dec 2025 12:22:35 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=cybertec.at; s=google; t=1766002953; x=1766607753; darn=lists.postgresql.org;
        h=mime-version:user-agent:content-transfer-encoding:references
         :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject
         :date:message-id:reply-to;
        bh=C0kRP4cysU2fzAZeH6hzWqc0OqxyDcGxbWGw20WrTOk=;
        b=BfQRPCfhaOfCIHPWfJvA9F9Yjv5Fah4P9NFsbNKutQbcpPKjywcBQBxoJqVcg032Ls
         yfNFg7jFiIfg9wvsNDPTMcCWJt3XBAAwh4vRswTItgoR/jZ13Wh+pI/KyPWeu3i8lek3
         MIMRtjPfeElMlDGDxyWXPcn55IzpBwbXK9EazCH5RrU2vjE3boheAZncj+I3duSE9ZEK
         mZCGaIGvYSIMSajUWdpdXUDFe0AxiqK+hGqdgCVX7Uc7Lvynv4fkxFyGeEWY/p+PkPbI
         2ZDoa4NHKT6ulJKHEnz8/8TmSfm+AwuQc+sJErGkSLJ2lOHv26pD5GphFef6uueK2imG
         uSXA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20230601; t=1766002953; x=1766607753;
        h=mime-version:user-agent:content-transfer-encoding:references
         :in-reply-to:date:cc:to:from:subject:message-id:x-gm-gg
         :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to;
        bh=C0kRP4cysU2fzAZeH6hzWqc0OqxyDcGxbWGw20WrTOk=;
        b=sdCLasEauqj6r1RkFGX02cURg9a1ql/xQfqRkb1X0o3HWccZEIeODXflQY4w7lLYbF
         vLDt/o9tC2pv+OQ2YI+Udzsu9PsrvE09qeFqtRRBQjSUZJDzgGIVZIbj/fPFubwB8Rao
         SsaRcbDS0WHQLBxe4MCxC3Or4C7e0uP3gR+Yiz0WNyMle9OTruCxGpKIh+7ufN7blCbH
         SxreasD/txUO+85Ec2XAW7lPhpUe7Rt1kcTOFmd9SsJowzd+N4M7DzpnEqzn3PqAQp6P
         cX0mtP7TPNhfrWB5VxyXebKuWUI9NwlhHQp9iiawEwZNSofqdt4mVa967jbuQzUfTc1M
         RZSg==
X-Gm-Message-State: AOJu0Yxkt7tbUQ89NRjI5gvh5ohsE1tIltktUCpP8fUJqxopwcF721Gp
	Mf2/nu9n5rOlb/vL6IgrFoqyvjImVb4g0Svlkzo8bNgqZ8vwC8W0D6kgKUcAc5J4uiw=
X-Gm-Gg: AY/fxX69FCjik7GiudH/hHlIHFaE6iO8PKgMBtzEp1f5OHiMpDyxHyZ5Dd3fkHGOCKe
	8MJPgIpt4pD7XJrHOf2/Zs7P7RbxfFcTGeEQacSE6gsXtHZijvuZDIwADrppczAbsiSeosasEKQ
	dX83vaabS/qCb9aMYO45crCQeBrxtjo87u14ZYBZBEOH5l/t4EyZViV7GAsrxu00NSdU51XXeDi
	5NSonexpHPI1ZikIiNZc8G26b18X/AhbjFSygl9SKJvmDxAhXuBMg8fD8x4/yDsMBsI/xFmPOeR
	InJM8AaHW62Q5p5JIcfVklKSLZukszPLdl1dU/VJkgBVgLtPbNIwidq2xn1Uej/irBb2uiTGA4/
	qbukjx4DIo5AMGlo+VJ79FKJVOkPYWL0Se7U+r1ibkhs6S3SUtOJbXMoI+rRwup2MmGANQXYANC
	T+ee+BgZAoLsewTo5OUT8+HtZqYdWOZj5rjZPJeO4/nCQ=
X-Google-Smtp-Source: AGHT+IHeM8hD3mbsmz39WglEpijAwnvU0vkYvjvgt51rUsRGFVi6JEo6maQOmF7o6Gxy+GX7iY8p6Q==
X-Received: by 2002:a05:600c:3f19:b0:479:398d:4bbf with SMTP id 5b1f17b1804b1-47a8f8c08a4mr188408165e9.13.1766002953067;
        Wed, 17 Dec 2025 12:22:33 -0800 (PST)
Received: from laurenz.albe-K4N0CV00F97414D ([2001:871:270:4cf9:1875:33eb:6694:f468])
        by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-47be3aa9971sm7775e9.13.2025.12.17.12.22.32
        (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256);
        Wed, 17 Dec 2025 12:22:32 -0800 (PST)
Message-ID: <3fd9c2859c323d6f894ccf660764f24dc3423181.camel@cybertec.at>
Subject: Re: libpq simple SELECT
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Igor Korot <ikorot01@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: Wed, 17 Dec 2025 21:22:32 +0100
In-Reply-To: <CA+FnnTzWJtJwUjWBbm9naV9BUWdzTnQ8dKCtPOqaL0+1UOWcAQ@mail.gmail.com>
References: 
	<CA+FnnTzP_9Mh9S7NJL5WFJmbOi2wxw4g=MdoUo-SuXEp3Fyg-g@mail.gmail.com>
	 <b92c6822df72932379da29ded319391b81cb2cf8.camel@cybertec.at>
	 <CA+FnnTzWJtJwUjWBbm9naV9BUWdzTnQ8dKCtPOqaL0+1UOWcAQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
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/3fd9c2859c323d6f894ccf660764f24dc3423181.camel%40cybertec.at>
Precedence: bulk

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