BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later - Mailing list pgsql-bugs

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.