BUG #18571: A CTE with a DELETE auxiliary statement only deletes when the DELETE results are referenced later

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.