From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: tilman.vogel@web.de
Subject: BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later
Date: Tue, 06 Aug 2024 18:51:12 +0000
Message-ID: <18571-4286b331d0b701f4@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18571
Logged by: Tilman Vogel
Email address: tilman.vogel@web.de
PostgreSQL version: 15.1
Operating system: Ubuntu 15.1-1.pgdg20.04+1
Description:
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.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, tilman.vogel@web.de
Subject: Re: BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later
In-Reply-To: <18571-4286b331d0b701f4@postgresql.org>
* 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