Thread: 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.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.

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