pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
3+ messages / 3 participants
[nested] [flat]
* BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
@ 2025-10-02 13:11 PG Bug reporting form <noreply@postgresql.org>
2025-10-02 20:13 ` Re: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work Tom Lane <tgl@sss.pgh.pa.us>
2025-10-03 09:34 ` RE: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work Maciej Walczak <m.walczak@megavision.pl>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2025-10-02 13:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: m.walczak@megavision.pl
The following bug has been logged on the website:
Bug reference: 19069
Logged by: Maciej Walczak
Email address: m.walczak@megavision.pl
PostgreSQL version: 18.0
Operating system: Windows 11 Pro 24H2
Description:
This doesn't actually do any locking:
BEGIN;
WITH my_lock AS (
SELECT pg_advisory_xact_lock(1)
)
INSERT INTO my_table (...) VALUES (...);
COMMIT;
To reproduce just run this code in two sessions in parallel without
commiting.
Both inserts will immediately complete without blocking.
I'm not sure if this is a bug. If it's not, perhaps the documentation should
be clarified.
Side-effects of WITH queries are mentioned here, so I would expect my code
to work:
https://www.postgresql.org/docs/16/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION
Tested on Postgres 18.0 and 16.10.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
2025-10-02 13:11 BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-02 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-10-02 20:13 UTC (permalink / raw)
To: m.walczak@megavision.pl; +Cc: pgsql-bugs@lists.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
2025-10-02 13:11 BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-03 09:34 ` Maciej Walczak <m.walczak@megavision.pl>
1 sibling, 0 replies; 3+ messages in thread
From: Maciej Walczak @ 2025-10-03 09:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thank you.
I missed those parts of the documentation. I should have studied it more carefully.
The reason I tried to use WITH is that a separate
SELECT pg_advisory_xact_lock(1);
statement confuses my object mapper (Dapper) when I want to use a RETURNING clause in the INSERT.
I found a workaround:
DO $$ BEGIN
PERFORM pg_advisory_xact_lock(1);
END $$;
Regards
Maciej Walczak
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 02 October 2025 22:14
To: Maciej Walczak <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
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-03 09:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-02 13:11 BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work PG Bug reporting form <noreply@postgresql.org>
2025-10-02 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-03 09:34 ` Maciej Walczak <m.walczak@megavision.pl>
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