From: Maciej Walczak <m.walczak@megavision.pl>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: RE: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
Thread-Topic: BUG #19069: pg_advisory_xact_lock() in a WITH query doesn't work
Thread-Index: AQHcM9kTDIsEO72xzUudoFWSfWL3I7SwKhzA
Date: Fri, 3 Oct 2025 09:34:39 +0000
Message-ID: <ae57b96787364f5892fb86e85e9e7118@megavision.pl>
References: <19069-f5c72e4e4f921a7c@postgresql.org>
 <8a06d047-45c8-4be5-9d39-f176bb8b62bc@HTXEX03.htx-net.pl>
In-Reply-To: <8a06d047-45c8-4be5-9d39-f176bb8b62bc@HTXEX03.htx-net.pl>
Accept-Language: pl-PL, en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ae57b96787364f5892fb86e85e9e7118%40megavision.pl>
Precedence: bulk

Thank you.
I missed those parts of the documentation. I should have studied it more ca=
refully.

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>=20
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 wo=
rk

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 th=
e 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