From: Tom Lane <tgl@sss.pgh.pa.us>
To: m.walczak@megavision.pl
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
Date: Thu, 02 Oct 2025 16:13:55 -0400
Message-ID: <1156183.1759436035@sss.pgh.pa.us> (raw)
In-Reply-To: <19069-f5c72e4e4f921a7c@postgresql.org>
References: <19069-f5c72e4e4f921a7c@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> This doesn't actually do any locking:
> BEGIN;
> WITH my_lock AS (
> SELECT pg_advisory_xact_lock(1)
> )
> INSERT INTO my_table (...) VALUES (...);
> COMMIT;
That looks as-expected to me. The docs say [1]
[ This works because ] PostgreSQL's implementation evaluates only
as many rows of a WITH query as are actually fetched by the parent
query.
which is to say, none at all in this case. There's also this in [2]:
Data-modifying statements in WITH are executed exactly once, and
always to completion, independently of whether the primary query
reads all (or indeed any) of their output. Notice that this is
different from the rule for SELECT in WITH: as stated in the
previous section, execution of a SELECT is carried only as far as
the primary query demands its output.
You're apparently expecting the presence of a volatile function
to cause the SELECT to be reclassified as data-modifying, but
we don't do it that way.
I'd recommend being less cute and just writing
BEGIN;
SELECT pg_advisory_xact_lock(1);
INSERT INTO my_table (...) VALUES (...);
COMMIT;
regards, tom lane
[1] https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CYCLE
[2] https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, m.walczak@megavision.pl, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
In-Reply-To: <1156183.1759436035@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox