Евгений — с телефоном: на экране отчёт свежести меню.

Евгений

Отчёт врёт. Строки с обновлённой ценой помечены «не трогали», и гости видят вчерашнюю цену.

Ты находишь причину в своём коммите: UPDATE цены есть, а updated_at — нет. А следом — письмо регулятора: нужен журнал «кто и когда меняет цены» (Латте: было 480, стало 500).

Павел

Оба случая — на триггер. База сама проставит время и сама запишет в журнал. Забудет человек — база не забудет.

Дмитрий

И спрячет. Обычный UPDATE тихо потянет запись, которой в коде не видно.

Т

Так за кем правда?

Павел

Я — про целостность. Время правки и журнал цен обязаны быть всегда. Кто пишет — неважно.

Дмитрий

А скидку, отгрузку, платёжный шлюз в базу не прячь — их место в приложении.

Т

Значит, граница — между «обязано для всех» и «зависит от сценария»?

Дмитрий

Её и проведём.

Спор сходится здесь. Обе боли закрывает серверная логика — триггеры, а разговор о функциях упирается в волатильность.

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-функции. Логично: индекс хранит вычисленное значение, и если функция может вернуть на тех же данных другое — индекс сразу «протухнет».

Насколько серьёзно это обещание, показывает история, которую Павел рассказывает новичкам.

Павел

Была у нас функция. 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.

А следующий модуль уже стартовал — сообщением в общий чат:

Эмиль (в чате)

Запускаем программу лояльности. Хочу, чтобы гости возвращались, а не платили раз и пропадали.

Баланс, начисления и списания лягут в одну базу — и собирать под них схему ты будешь уже в 10-01.