В Brew две повторяющиеся боли. Первая: поле updated_at. Кто-то из бэкендеров обновил заказ и забыл проставить updated_at — и теперь у строки «время изменения» врёт, аналитика по свежести данных битая. Хочется, чтобы это поле заполнялось само, без надежды на дисциплину каждого, кто пишет UPDATE. Вторая: аудит. Регулятор просит журнал «кто что менял в ценах»: было 480, стало 500. Прописывать запись в журнал в каждом месте кода, где меняется цена, — значит рано или поздно где-то забыть.

Обе боли закрывает серверная логика — триггеры: функция, которую база сама вызывает на каждый INSERT/UPDATE/DELETE. А разговор про функции упирается в понятие волатильности — обещание базе, насколько функция предсказуема.

BEFORE-триггер: правит строку до записи

BEFORE-триггер срабатывает до того, как строка ляжет на диск, и может её изменить — для этого он возвращает изменённый NEW. Классика — автозаполнение updated_at:

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();   -- меняем строку ДО записи
    RETURN NEW;                -- BEFORE обязан вернуть строку для записи
END;
$$;
CREATE TRIGGER touch_lab_bupd
    BEFORE UPDATE ON touch_lab
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Теперь любой UPDATE строки сам проставит updated_at = now(), и «забыть» это поле физически нельзя — заполнение переехало из кода приложения в инвариант таблицы. Возврат NEW обязателен: именно его база и запишет.

AFTER-триггер: видит OLD и NEW, пишет аудит

AFTER-триггер срабатывает после записи; менять строку уже поздно, его возврат игнорируется. Зато ему доступны обе версии строки: OLD (как было) и NEW (как стало). Это и есть материал для аудита. По TG_OP (тип операции) триггер решает, что записать в журнал:

IF TG_OP = 'INSERT' THEN ...     -- OLD нет (строки раньше не было)
ELSIF TG_OP = 'UPDATE' THEN ...  -- есть и OLD, и NEW
ELSIF TG_OP = 'DELETE' THEN ...  -- NEW нет (строки больше не будет)

Важная асимметрия: в INSERT нет OLD (нечего было), в DELETE нет NEW (ничего не останется). В журнале это видно как ∅. Перекличка с 03-05: там RETURNING old/new отдавал обе версии вызывающему запросу; здесь триггер ловит их на стороне базы и пишет независимо от того, кто и откуда дёрнул UPDATE.

Что доступно триггеру по TG_OP:
  INSERT   OLD = ∅            NEW = {новая строка}    строки раньше не было
  UPDATE   OLD = {как было}   NEW = {как стало}       есть обе версии
  DELETE   OLD = {как было}   NEW = ∅                 строки больше не будет

Волатильность: обещание планировщику

Каждая функция в Postgres несёт метку волатильности — обещание планировщику, насколько она предсказуема:

Метка — это обещание, на которое опирается планировщик; соврать в ней опасно (см. заборчик). Самое наглядное следствие: в индексное выражение пускают только IMMUTABLE-функции. Логично: индекс хранит вычисленное значение, и если функция может вернуть на тех же данных другое — индекс сразу «протухнет».

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

Это escape-hatch psql-юнит (как 05-02/08-04): тема — серверный DDL и PL/pgSQL, sqlc неприменим. demo.sql в трёх частях: BEFORE-триггер сам бампит updated_at; AFTER-триггер пишет аудит с OLD/NEW для INSERT/UPDATE/DELETE; затем три функции с метками IMMUTABLE/STABLE/VOLATILE — их классификацию читаем из каталога pg_proc, а попытку построить индекс по VOLATILE-функции ловим как ошибку. Функция f_vol_int намеренно на plpgsql: тривиальную sql-функцию планировщик «встроил» бы в выражение и метка потерялась бы — plpgsql-функции не встраиваются, поэтому метка остаётся в силе.

Запуск

docker compose up -d
make lecture L=09-writes-eventing-and-server-logic/09-05-triggers-and-volatility T=db-reset
make lecture L=09-writes-eventing-and-server-logic/09-05-triggers-and-volatility

T=run — режим по умолчанию, его можно не писать. Изнутри каталога юнита короче: make db-reset, затем make run. Сырой текст ошибки шага 3 уходит в stderr; в stdout ниже остаётся детерминированный SQLSTATE.

1) BEFORE-триггер сам проставил updated_at на UPDATE (печатаем факт, не значение):
 id |       name       | updated_at_bumped 
----+------------------+-------------------
  1 | Эспрессо (1 шот) | t
 
 
2) AFTER-триггер записал аудит (∅ = значения нет: OLD в INSERT, NEW в DELETE):
   op   | old_name | new_name | old_price | new_price 
--------+----------+----------+-----------+-----------
 INSERT | ∅        | Латте    | ∅         | 480
 UPDATE | Латте    | Латте    | 480       | 500
 DELETE | Латте    | ∅        | 500       | ∅
 
 
3) Как Postgres классифицировал наши функции (provolatile из каталога):
 proname | volatility 
---------+------------
 f_imm   | IMMUTABLE
 f_stb   | STABLE
 f_vol   | VOLATILE
 
 
   f_imm (IMMUTABLE) в индексном выражении — можно:
          result           
---------------------------
 индекс по f_imm(n) создан
 
   f_vol_int (VOLATILE) в индексном выражении — нельзя (сырой текст ошибки в stderr):
SQLSTATE = 42P17 (functions in index expression must be marked IMMUTABLE)

BEFORE-триггер сам поднял updated_at (флаг t). AFTER-триггер записал три строки аудита, и в них видна асимметрия: у INSERT пусто старое, у DELETE — новое. Функции классифицированы как i/s/v, и индекс по IMMUTABLE собрался, а по VOLATILE отбит кодом 42P17.

Заборчик: когда логику в БД класть НЕ надо

• Логика становится невидимой. Простой UPDATE тихо тянет за собой запись в аудит, бамп updated_at, может и NOTIFY (09-04) — разработчик, читающий код приложения, об этом не узнает, пока не упрётся в неожиданный эффект. Триггеры тяжело тестировать, тяжело версионировать вместе с кодом, и каскад «триггер дёргает триггер» отлаживается мучительно.
• Граница: инварианты — в БД, бизнес-логику — в приложение. Триггеры держим для инвариантов данных (updated_at, аудит, проверки целостности, которые обязаны выполняться независимо от того, какой сервис пишет). Бизнес-логику (посчитать скидку, решить, можно ли отгрузить заказ, позвать платёжный шлюз) оставляем в приложении: её там видно, её просто тестировать, и она не блокирует запись в таблицу на время исполнения. Эмпирика: «обязательно для ВСЕХ, кто трогает эту таблицу, и про целостность» → можно в БД; «зависит от сценария, ходит наружу, меняется часто» → в приложении.
• Не ври в метке волатильности. Пометь функцию IMMUTABLE, а внутри читай таблицу — планировщик закэширует первый результат и будет отдавать устаревший; такой баг не воспроизводится «на ровном месте» и ищется днями. Метка должна быть честной: функция, читающая данные, — максимум STABLE, пишущая или недетерминированная — VOLATILE.
• Глубина PL/pgSQL (курсоры, исключения, динамический SQL, производительность серверных функций) — это уже отдельная большая тема на стыке с твоим DBA; в этом курсе мы держим серверную логику на «средней» глубине.

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

Триггеры переносят инвариант с уровня дисциплины разработчика на уровень таблицы: BEFORE правит строку до записи (автозаполнение updated_at, возврат NEW обязателен), AFTER видит OLD и NEW и идеален для аудита (в INSERT нет OLD, в DELETE нет NEW). Волатильность — обещание планировщику: IMMUTABLE/STABLE/VOLATILE, и только IMMUTABLE пускают в индексное выражение; врать в метке нельзя. И главное правило модуля: серверная логика — для инвариантов данных, а не для бизнес-логики; то, что зависит от сценария и ходит наружу, живёт в приложении, где оно видно и тестируемо.

На этом модуль 09 закрыт: мы научились писать пачкой (MERGE/COPY), раздавать работу (SKIP LOCKED), надёжно порождать события (outbox), толкать сигналы (NOTIFY) и переносить инварианты в БД (триггеры). Дальше — модуль 10, капстоны: всё это связывается в сквозные сценарии, включая 10-05, где наш outbox и базовые таблицы Brew уезжают через CDC в соседний kafka-cookbook.