RETURNING old/new

Заказ Brew меняет статус: created → paid → shipped. Для аудита и для уведомлений приложению нужно знать не только новое значение, но и прежнее: «было created, стало paid». Классически за этим лезут двумя путями — либо SELECT статуса до UPDATE (лишний запрос плюс гонка: между чтением и записью статус мог измениться), либо триггер, который в отдельной таблице пишет OLD/NEW (мощно, но это уже логика на стороне БД — см. модуль 09).

PG18 добавил третий, прямой путь: в RETURNING теперь можно ссылаться на строку до изменения и после — через префиксы old. и new.. UPDATE ... RETURNING old.status, new.status возвращает оба значения одной командой, в той же транзакции, без второго запроса и без триггера.

old и new в RETURNING

До PG18 RETURNING отдавал строку в её итоговом виде (после изменения). Теперь у каждой колонки есть две «версии»:

new.col — значение после команды (для UPDATE/INSERT);
old.col — значение до команды (для UPDATE/DELETE).

Без префикса (просто status) сохраняется старое поведение: для UPDATE/INSERT это new, для DELETE — old. Можно возвращать и выражения над обеими версиями: (old.paid_at IS NULL), new.amount - old.amount и т.п.

Симметрия INSERT / UPDATE / DELETE

Префиксы раскрывают красивую симметрию трёх команд через «есть ли строка до и после»:

INSERT — строки «до» нет, поэтому old.* всюду NULL; new.* — то, что вставили.
UPDATE — существуют обе версии: old.* (до) и new.* (после).
DELETE — строки «после» нет, поэтому new.* всюду NULL; old.* — то, что удалили.

То есть old/new — это единый язык для «что было и что стало» поверх любой модифицирующей команды.

Матрица old/new: симметрия трёх команд

У каждой строки две «версии» — до команды (old) и после (new). Какая из них существует, решает сама команда:

plaintext

            old.* (до)        new.* (после)
          ┌───────────────┬───────────────┐
 INSERT   │    ∅  нет     │  вставленная  │  строки «до» не было
          ├───────────────┼───────────────┤
 UPDATE   │   прежняя     │  изменённая   │  существуют обе
          ├───────────────┼───────────────┤
 DELETE   │  удалённая    │    ∅  нет     │  строки «после» не осталось
          └───────────────┴───────────────┘
              ▲                          ▲
    без префикса DELETE → old   без префикса INSERT/UPDATE → new

Команда	`old.*` (до)	`new.*` (после)	Колонка без префикса
`INSERT`	`NULL` (строки «до» нет)	вставленная строка	= `new`
`UPDATE`	прежняя версия	изменённая версия	= `new`
`DELETE`	удалённая строка	`NULL` (строки «после» нет)	= `old`

Почему этот юнит — без sqlc

Остальные CRUD-юниты модуля написаны через sqlc, но здесь sqlc мешает: его парсер (версия v1.30.0) ещё не знает PG18-синтаксис old./new. и падает с ошибкой column "status" does not exist. А урок именно про эту фичу. Поэтому юнит — escape-hatch: пишем запросы строкой и сканируем результат руками через pgx (как в 00-04/00-06), без query.sql и сгенерированного internal/db. Когда уроку нужна возможность БД, которую инструмент ещё не поддерживает, мы выбираем возможность, а не инструмент.

Что показывает наш код

В main.go три модифицирующие команды, каждая с RETURNING old/new. UPDATE (обе версии существуют):

pool.QueryRow(ctx, `
    UPDATE order_status_lab SET status = 'paid', paid_at = now()
    WHERE id = 1
    RETURNING old.status, new.status,
              (old.paid_at IS NULL)     AS was_unpaid,
              (new.paid_at IS NOT NULL) AS now_paid`,
).Scan(&oldStatus, &newStatus, &wasUnpaid, &nowPaid)

И INSERT / DELETE, где одна сторона пуста:

sql

INSERT INTO order_status_lab (id, status) VALUES (4, 'created')
RETURNING old.status, new.status;   -- old.status → NULL (строки «до» нет)
 
DELETE FROM order_status_lab WHERE id = 2
RETURNING old.status, new.status;   -- new.status → NULL (строки «после» нет)

«Пустую» сторону (NULL) в выводе мы печатаем как ∅. Демо работает на лабораторном столе order_status_lab, который пересоздаётся в начале (CREATE IF NOT EXISTS + TRUNCATE + три заказа) — вывод детерминирован.

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=03-crud-fluency/03-05-returning-old-new T=db-reset
make lecture L=03-crud-fluency/03-05-returning-old-new

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

plaintext

1) Стол order_status_lab засеян: заказы #1, #2, #3 в статусе 'created'.
 
2) UPDATE #1: created → paid (RETURNING old/new одним запросом):
   old.status=created  new.status=paid   было неоплачено=true  стало оплачено=true
 
3) INSERT #4 'created' (RETURNING old/new):
   old.status=∅  new.status=created   → на INSERT строки «до» нет, old.* пуст
 
4) DELETE #2 (RETURNING old/new):
   old.status=created  new.status=∅   → на DELETE строки «после» нет, new.* пуст

UPDATE отдал и старый статус (created), и новый (paid), и два предиката над обеими версиями (was_unpaid, now_paid) — всё одним запросом. INSERT показал пустой old.* (строки до не было), DELETE — пустой new.* (строки после не осталось). Та самая симметрия «до/после».

Заборчик

RETURNING old/new отдаёт «до и после» только для тех строк, которые эта команда трогает, и только в её транзакции — это не журнал изменений. Отсюда границы:

Это не замена аудиту. Когда историю надо хранить независимо от того, какой код сделал запись (и для UPDATE из любого места, и для прямых правок в psql), берут триггер AFTER с OLD/NEW, пишущий в audit-таблицу — это модуль 09-05, там же обсудим, когда логику стоит держать в БД, а когда нет.
old/new — фича именно PG18. На более старых версиях её нет: «до-значение» добывают SELECT-ом до UPDATE (с риском гонки) или триггером.
sqlc (v1.30.0) её пока не понимает — поэтому в проде с sqlc такой запрос придётся либо писать «сырым» через pgx (как здесь), либо ждать поддержки в инструменте.

Что забрать с собой

PG18: в RETURNING можно ссылаться на строку до изменения (old.col) и после (new.col) — оба значения одной командой.
UPDATE даёт обе версии; на INSERT пуст old.* (строки «до» нет), на DELETE пуст new.* (строки «после» нет).
Без префикса колонка ведёт себя как раньше: new для UPDATE/INSERT, old для DELETE.
Это убирает «SELECT до UPDATE» (лишний запрос + гонка) для аудита перехода в рамках одной команды.
Это не замена полноценному аудиту (триггер + history-таблица, → 09-05) и пока не поддержано sqlc — отсюда raw-pgx в этом юните.

Дальше — юнит 03-06 «трезвая семантика NULL»: расплата за тизер из 01-02 — ловушка NOT IN с NULL, которая молча возвращает «ничего», и инструменты COALESCE/NULLIF/IS DISTINCT FROM, которыми с NULL работают безопасно.

Этот урок ещё впереди