Why delete and insert in a CTE works despite UNIQUE constraint?

Question

Given a table with a unique constraint on a column

CREATE TABLE t
(
    id   int GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL UNIQUE
);

Let's create a single record

INSERT INTO t (name) VALUES ('a');

When I try to delete an existing record and insert a new one with the same value for the unique column in a single statement using CTE, it fails as expected by me:

WITH
    deleted_cte AS (DELETE FROM t WHERE name = 'a' RETURNING id),
    inserted_cte AS (INSERT INTO t (name) VALUES ('a') RETURNING id)
SELECT 1;

-- ERROR:  duplicate key value violates unique constraint "t_name_key"
-- DETAIL:  Key (name)=(a) already exists.

I expect DELETE and INSERT commands to run concurrently in an unspecified order and see the same snapshot of the table.

However, if I introduce dependency between the primary query and the deleting sub-statement, it works:

WITH
    deleted_cte AS (DELETE FROM t WHERE name = 'a' RETURNING id),
    inserted_cte AS (INSERT INTO t (name) VALUES ('a') RETURNING id)
SELECT id from deleted_cte; -- only this line modified
-- 1 <- returns id of the deleted record 

SELECT id FROM t;
-- 2 <- inserted record

-- Plan of the query:
-- CTE Scan on deleted_cte
--   CTE deleted_cte
--     ->  Delete on t
--           ->  Seq Scan on t
--                 Filter: (name = 'a'::text)
--   CTE inserted_cte
--     ->  Insert on t t_1
--           ->  Result

I do not understand what happens here.

Are sub-statements ordered now and INSERT is forced to run after DELETE?
Is UNIQUE check somehow moved to the end of the whole CTE?

Where in the docs can I read about this behavior or is it an unreliable thing?

https://dbfiddle.uk/FYrW2n5W

---

I realize that this exact delete-insert can be replaced with upsert.

But in the actual code records aren't hard deleted. They instead are being soft deleted using UPDATE t SET deleted_at = now() and then new records inserted. Unique constraint filters out soft deleted records CREATE UNIQUE INDEX ON t (name) WHERE (deleted_at IS NULL). So upsert wouldn't work here.

Answer 1

The documentation states

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable.

I would say that the only way to force a certain execution order would be to make the inserting CTE depend on the deleting one, like in

WITH
    deleted_cte AS (DELETE FROM t WHERE name = 'a' RETURNING id),
    inserted_cte AS (INSERT INTO t (name) SELECT 'a' FROM deleted_cte RETURNING id)
...