public inbox for [email protected]
help / color / mirror / Atom feedSELECT FOR UDPATE behavior inside joins
4+ messages / 3 participants
[nested] [flat]
* SELECT FOR UDPATE behavior inside joins
@ 2025-12-29 09:15 Khan, Tanzeel <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Khan, Tanzeel @ 2025-12-29 09:15 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT FOR UDPATE behavior inside joins
@ 2025-12-31 15:33 Tom Lane <[email protected]>
parent: Khan, Tanzeel <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-12-31 15:33 UTC (permalink / raw)
To: Khan, Tanzeel <[email protected]>; +Cc: [email protected] <[email protected]>
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT FOR UDPATE behavior inside joins
@ 2025-12-31 15:35 Adrian Klaver <[email protected]>
parent: Khan, Tanzeel <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2025-12-31 15:35 UTC (permalink / raw)
To: Khan, Tanzeel <[email protected]>; [email protected] <[email protected]>
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
[email protected]
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 <[email protected]>)
id 1vVy2h-00A9P9-0V
for [email protected];
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 <[email protected]>)
id 1vVy2g-00GLAS-04
for [email protected];
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 <[email protected]>)
id 1vVy2f-00GLAJ-28
for [email protected];
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 <[email protected]>)
id 1vVy2d-001IAs-1q
for [email protected];
Wed, 17 Dec 2025 20:22:37 +0000
Received: by mail-wm1-x343.google.com with SMTP id 5b1f17b1804b1-4779aa4f928so73076525e9.1
for <[email protected]>; 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: <[email protected]>
Subject: Re: libpq simple SELECT
From: Laurenz Albe <[email protected]>
To: Igor Korot <[email protected]>
Cc: "pgsql-generallists.postgresql.org" <[email protected]>
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>
<[email protected]>
<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:[email protected]>
List-Owner: <mailto:[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT FOR UDPATE behavior inside joins
@ 2026-01-01 08:40 Khan, Tanzeel <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Khan, Tanzeel @ 2026-01-01 08:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-01-01 08:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-29 09:15 SELECT FOR UDPATE behavior inside joins Khan, Tanzeel <[email protected]>
2025-12-31 15:33 ` Tom Lane <[email protected]>
2026-01-01 08:40 ` Khan, Tanzeel <[email protected]>
2025-12-31 15:35 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox