From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | tilman(dot)vogel(at)web(dot)de |
Subject: | BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later |
Date: | 2024-08-06 18:51:12 |
Message-ID: | 18571-4286b331d0b701f4@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18571
Logged by: Tilman Vogel
Email address: tilman(dot)vogel(at)web(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-08-06 18:54:25 | Re: BUG #18545: \dt breaks transaction, calling error when executed in SET SESSION AUTHORIZATION |
Previous Message | Robert Haas | 2024-08-06 18:48:06 | Re: BUG #18545: \dt breaks transaction, calling error when executed in SET SESSION AUTHORIZATION |