From: Tom Lane <tgl@sss.pgh.pa.us>
To: tilman.vogel@web.de
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later
Date: Tue, 06 Aug 2024 16:55:15 -0400
Message-ID: <1831308.1722977715@sss.pgh.pa.us> (raw)
In-Reply-To: <18571-4286b331d0b701f4@postgresql.org>
References: <18571-4286b331d0b701f4@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> The following code fails with "psycopg.errors.UniqueViolation: duplicate key
> value violates unique constraint "test_pkey" DETAIL: Key (name)=(key1)
> already exists."
> CREATE TEMPORARY TABLE test(name TEXT PRIMARY KEY, value TEXT);
> INSERT INTO test(name, value) VALUES('key1', 'value1');
> WITH deleted AS (
> DELETE FROM test
> WHERE name = 'key1'
> RETURNING *
> )
> INSERT INTO test(name, value) VALUES('key1', 'value1');
> However, this succeeds:
> CREATE TEMPORARY TABLE test(name TEXT PRIMARY KEY, value TEXT);
> INSERT INTO test(name, value) VALUES('key1', 'value1');
> WITH deleted AS (
> DELETE FROM test
> WHERE name = 'key1'
> RETURNING *
> ),
> inserted AS (
> INSERT INTO test(name, value) VALUES('key1', 'value1')
> RETURNING *
> )
> SELECT NULL FROM deleted;
> So, I am puzzled whether this optimization that the "deleted" sub-expression
> is not evaluated at all when not used later which breaks the second
> sub-expression is to be expected.
I don't see any bug here. The executor will run DML
(INSERT/UPDATE/DELETE) WITH clauses to completion whether they are
read by the outer query or not --- but if they are not, that happens
after completion of the outer query. In your first example, the
INSERT happens first and so it fails. (You could have avoided that
by making the pkey constraint deferrable, but you didn't.) In the
second example, the "deleted" subquery is run to completion as a
side-effect of completing the outer query, and then the "inserted"
subquery gets run, so there's no error.
Our SELECT man page says that
The primary query and the WITH queries are all (notionally)
executed at the same time. This implies that the effects of a
data-modifying statement in WITH cannot be seen from other parts
of the query, other than by reading its RETURNING output. If two
such data-modifying statements attempt to modify the same row, the
results are unspecified.
I'd argue that the last sentence covers the fact that these examples
behave oddly. Perhaps we ought to mention the fact that constraint
violations provide a separate pathway for WITH sub-queries to affect
each other; but if such a violation could happen then you're already
doing something that's explicitly stated to be unspecified. There's
also relevant disclaimers in the main docs:
https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
regards, tom lane
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, tilman.vogel@web.de, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later
In-Reply-To: <1831308.1722977715@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