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

ОБЯЗАТЕЛЬНО

Бэкенду

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

---

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

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

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

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

---

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

CREATE OR REPLACE FUNCTION orders_total(p_customer_id bigint)
RETURNS numeric
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_total numeric;
BEGIN
  SELECT COALESCE(SUM(amount), 0) INTO v_total
  FROM orders
  WHERE customer_id = p_customer_id
    AND status = 'paid';

  RETURN v_total;
END;
$$;

Процедуры (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.

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  table_name text,
  row_id bigint,
  action text,
  changed_at timestamptz DEFAULT now(),
  old_data jsonb,
  new_data jsonb
);

CREATE OR REPLACE FUNCTION audit_orders()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log (table_name, row_id, action, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_orders();

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

---

Statement-level triggers

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

---

Event triggers

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

CREATE OR REPLACE FUNCTION reject_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'DROP TABLE запрещён в production';
  END IF;
END;
$$;

CREATE EVENT TRIGGER protect_drop
  ON sql_drop
  EXECUTE FUNCTION reject_drop();

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

---

NOTIFY и LISTEN

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

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

CREATE OR REPLACE FUNCTION notify_order_paid()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.status = 'paid' AND OLD.status IS DISTINCT FROM 'paid' THEN
    PERFORM pg_notify(
      'order_events',
      json_build_object('id', NEW.id, 'status', NEW.status)::text
    );
  END IF;
  RETURN NEW;
END;
$$;

Подписчик (отдельное соединение, 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 — триггеры — синтаксис в общем виде

---