Зачем класть мутации внутрь WITH

До сих пор WITH содержал только SELECT-ы. PostgreSQL (начиная с версии 9.1) позволяет нечто более интересное — внутри WITH могут стоять INSERT, UPDATE, DELETE с RETURNING. Это даёт возможность связать несколько модификаций в один SQL-стейтмент, который выполняется атомарно.

Сценарии, в которых это нужно:

• Архивация перед удалением: переложить строки из живой таблицы в архивную, затем удалить из живой — за один запрос.
• Иерархическое создание: вставить родительскую запись, получить её id, тут же вставить дочерние с этим id.
• Trade-off между UPDATE и INSERT: проверить наличие строки, обновить если есть, вставить если нет — всё одним стейтментом.

Без data-modifying CTE такие вещи делаются через явную транзакцию (BEGIN, два-три запроса, COMMIT) и обычно требуют дополнительного round-trip к серверу. Data-modifying CTE сворачивает это в один стейтмент — меньше latency, меньше сетевых походов, проще rollback.

Простейший пример — DELETE + RETURNING

RETURNING — это атрибут PostgreSQL у INSERT, UPDATE, DELETE. Он позволяет вернуть значения изменённых строк как результат запроса.

Что произошло: DELETE отработал и вернул удалённые строки через RETURNING. Они стали «таблицей» deleted, и финальный SELECT соединил их с customers, чтобы показать, кому принадлежали удалённые заказы.

В одной транзакции с одним стейтментом. Если что-то пойдёт не так — rollback всего блока, как обычная PostgreSQL-транзакция.

INSERT с RETURNING — поймать сгенерированный id

Самый частый паттерн: вставил строку, получил id, вставил дочерние строки.

Заметь две вещи:

1. Сгенерированный id нового заказа доступен в new_order через RETURNING. Без этого пришлось бы делать второй запрос с currval('orders_id_seq') или явно искать.
2. Финальный оператор — это сам INSERT INTO order_items. Он тоже использует RETURNING, и его результат — вывод всего стейтмента.

В традиционной форме это потребовало бы 3 запроса и явной транзакции на клиенте.

Семантика snapshot — что видит финальный SELECT

Это самый важный нюанс, который ломает наивные ожидания.

Все CTE одного WITH выполняются с одним snapshot базы данных. То есть, если ты в одном CTE удалил строку, а в другом CTE сделал SELECT из той же таблицы — второй CTE всё ещё видит удалённую строку. Изменения от data-modifying CTE становятся видны только после завершения всего стейтмента.

Практический вывод: никогда не пытайся “увидеть результат изменения” обычным SELECT в том же WITH. Только через RETURNING из самого модифицирующего CTE.

Если ты ожидал, что visible_in_snapshot уже не учитывает удалённую строку — пересмотри ожидания. PostgreSQL так не работает.

Порядок выполнения и UPDATE+INSERT

UPDATE внутри WITH ведёт себя аналогично. Можно поймать обновлённые строки через RETURNING и использовать их в следующем шаге.

Что бы это выглядело в боевой системе: ты переводишь заказ в статус shipped, выбираешь данные клиента, отправляешь ему уведомление — всё это попадает в один SQL-стейтмент с одной транзакцией, и приложение получает готовый ответ.

Атомарность всего стейтмента

Главное обещание WITH с data-modifying CTE: либо все изменения применяются, либо ни одно. Это полноценная транзакционная семантика, даже если ты не оборачивал запрос в BEGIN/COMMIT. Каждый SQL-стейтмент в PostgreSQL — это атомарная транзакция по умолчанию.

Если в середине твоего WITH сработает constraint violation (например, foreign key), вся пачка изменений откатится. Если в середине упало деление на ноль в финальном SELECT — откатится. Это сильное гарантированное свойство, на которое можно опираться при проектировании.

Тонкость: порядок выполнения внутри WITH не гарантирован

Если у тебя в одном WITH два модифицирующих CTE, в которых порядок логически важен — будь осторожен. PostgreSQL не гарантирует, в каком порядке они выполнятся, кроме случаев, когда один зависит от RETURNING другого.

Например:

WITH a AS (UPDATE t SET x = 1 WHERE id = 5 RETURNING *),
     b AS (UPDATE t SET x = 2 WHERE id = 5 RETURNING *)
SELECT * FROM a, b;

Финальное значение t.id=5.x непредсказуемо — может быть 1, может быть 2. Это не баг, это специально не определено в стандарте. Если порядок важен — пиши явный пайплайн с зависимостями через RETURNING.

Чек-лист

• В WITH можно класть INSERT, UPDATE, DELETE с RETURNING — это PostgreSQL-расширение.
• RETURNING — единственный канал передачи данных от модифицирующего CTE к остальным частям запроса.
• Все CTE одного WITH работают на одном snapshot базы. Обычный SELECT НЕ видит изменений от соседних CTE.
• Стейтмент атомарен: либо все изменения применились, либо ни одно. Транзакция вокруг не нужна (но и не мешает).
• Порядок выполнения нескольких независимых модифицирующих CTE не гарантирован. Если он важен — выражай его явно через RETURNING-зависимости.