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

PG Bug reporting form <noreply(at)postgresql(dot)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