PL/pgSQL, триггеры и NOTIFY/LISTEN в PostgreSQL

ОБЯЗАТЕЛЬНО

Бэкенду

Раздел 8.11, шаг 5 из 12. Дальше — репликация и PgBouncer.

---

Когда логика живёт в СУБД

Бизнес-правила можно держать в приложении или в PostgreSQL. PL/pgSQL уместен, когда нужно:

• атомарность правила с данными в одной транзакции;
• аудит всех изменений независимо от клиента;
• сложные batch-операции ближе к данным (меньше round-trip);
• NOTIFY подписчикам при изменении.

Перегруз СУБД процедурами усложняет тестирование и деплой — баланс с микросервисами.

---

PL/pgSQL — функции и процедуры

Код ITЗагрузка примера кода…

Процедуры (PostgreSQL 11+) могут commit внутри — для многошаговых ETL без одной длинной транзакции:

CREATE PROCEDURE archive_old_orders(p_before date)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < p_before;
  DELETE FROM orders WHERE created_at < p_before;
  COMMIT;
END;
$$;

Конструкция	Назначение
DECLARE	Локальные переменные
BEGIN … EXCEPTION … END	Обработка ошибок
RETURN QUERY	Set-returning functions
%ROWTYPE, %TYPE	Привязка к схеме таблицы
RAISE NOTICE/WARNING/EXCEPTION	Лог и прерывание

---

Row-level triggers

Срабатывают на каждую строку BEFORE/AFTER INSERT|UPDATE|DELETE.

Код ITЗагрузка примера кода…

BEFORE trigger может изменить NEW или отменить операцию (RETURN NULL).

---

Statement-level triggers

FOR EACH STATEMENT — один раз на SQL-команду. Подходят для агрегированного аудита или блокировки DDL в окне обслуживания.

---

Event triggers

Event triggers реагируют на DDL (не DML) — CREATE TABLE, DROP FUNCTION, …

Код ITЗагрузка примера кода…

Используют для политики схемы, логирования миграций, запрета опасных команд.

---

NOTIFY и LISTEN

Pub/sub внутри PostgreSQL — канал с именем и текстовым payload (до ~8000 байт).

Отправка (обычно из trigger):

Код ITЗагрузка примера кода…

Подписчик (отдельное соединение, blocking):

LISTEN order_events;
-- в psql уведомления появляются асинхронно

В приложении (Python psycopg):

conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur.execute("LISTEN order_events;")
while True:
    conn.poll()
    while conn.notifies:
        n = conn.notifies.pop(0)
        handle(n.payload)
    time.sleep(0.1)

Без polling из бэкенда

LISTEN держит одно долгоживущее соединение на воркер. Для многих инстансов приложения используйте очередь (Kafka, RabbitMQ) или fan-out через один relay-сервис. NOTIFY подходит для простых сценариев и cache invalidation.

Ограничения NOTIFY:

• payload не персистентный — offline-подписчик пропустит событие;
• нет гарантии порядка между каналами;
• при pg_notify в rolled-back транзакции уведомление не уходит.

---

Языки кроме PL/pgSQL

Язык	Когда
SQL	Простые функции одним запросом
PL/Python	numpy, сложная логика (нужно доверие к plpythonu)
PL/pgSQL	Default для процедур в Postgres

---

Практика

1. Реализуйте audit trigger на учебной таблице, проверьте записи при INSERT/UPDATE/DELETE.
2. NOTIFY при смене статуса заказа; отдельная сессия psql с LISTEN.
3. Event trigger, логирующий CREATE TABLE в служебную таблицу.

---

Связанные материалы

• Асинхронная коммуникация — брокеры сообщений
• Push, Pull, Webhooks
• SQL — триггеры — синтаксис в общем виде

---