PostgreSQL
Функции и процедуры: PL/pgSQL
Приложение делает 8 запросов к БД для расчёта скидки: проверяет историю заказов, статус, промо-коды, регион. Каждый запрос - round-trip через сеть. Одна PL/pgSQL-функция делает то же самое за один вызов, полностью внутри БД. Это не преждевременная оптимизация - это архитектурное решение о том, где живёт бизнес-логика.
- **GitLab:** разрешения на доступ к проектам рассчитываются через PL/pgSQL-функции; один вызов вместо цепочки запросов из Ruby снижает latency на 30-50% на 5+ млн активных проектов
- **Supabase:** SECURITY DEFINER-функции - основной паттерн для Row Level Security API; пользователи работают через функции, не имея прямого доступа к таблицам
- **Shopify:** триггеры для аудит-лога всех изменений заказов; при транзакции из 10+ INSERT/UPDATE триггер записывает в audit_log атомарно вместе с основными данными без риска рассинхронизации
PL/pgSQL: процедурный язык PostgreSQL
PL/pgSQL - процедурный язык, встроенный в PostgreSQL. Позволяет писать функции с переменными, условиями, циклами и обработкой исключений. Функция выполняется внутри сервера - нет round-trip между приложением и БД для каждой операции.
Используй `CREATE OR REPLACE FUNCTION` - это атомарная замена без DROP+CREATE. Тело функции - обычная строка (долларовые кавычки `$$...$$` избавляют от экранирования одинарных кавычек внутри). Всегда указывай `LANGUAGE plpgsql` явно.
Gitlab хранит в PL/pgSQL функциях логику расчёта разрешений (project visibility, access levels). Вызов одной функции вместо 5-10 отдельных запросов из Ruby снижает latency на 30-50% для высоконагруженных endpoints.
Зачем использовать долларовые кавычки (`$$`) вместо обычных кавычек для тела функции?
Триггеры: автоматические действия на события
Триггер - функция, автоматически вызываемая при INSERT/UPDATE/DELETE/TRUNCATE на таблице. Триггер не может быть вызван напрямую - только как реакция на событие. В PL/pgSQL триггерная функция всегда возвращает `TRIGGER` и имеет доступ к `NEW` и `OLD`.
Типы триггеров: BEFORE (до операции, можно изменить NEW или отменить через NULL), AFTER (после, NEW/OLD только для чтения), INSTEAD OF (для представлений). FOR EACH ROW срабатывает для каждой строки, FOR EACH STATEMENT - один раз на оператор.
Триггерная функция типа BEFORE UPDATE возвращает NULL. Что произойдёт?
RETURNS TABLE: функции как источники данных
`RETURNS TABLE` позволяет функции возвращать набор строк - как таблица или VIEW, но с параметрами и встроенной логикой. Такие функции называют Table-Valued Functions (TVF) и используют в FROM-клаузе как обычные таблицы.
SQL-функции (`LANGUAGE sql`) инлайнятся оптимизатором - их код вставляется в вызывающий запрос и оптимизируется как единое целое. PL/pgSQL-функции - чёрный ящик для планировщика. Если функция - просто обёртка над одним SELECT, предпочти `LANGUAGE sql`.
Почему SQL-функция (`LANGUAGE sql`) может выполняться быстрее аналогичной PL/pgSQL-функции?
SECURITY DEFINER: запуск от имени создателя
По умолчанию функция выполняется с правами вызывающего пользователя (`SECURITY INVOKER`). `SECURITY DEFINER` меняет это: функция выполняется с правами её создателя. Это удобный механизм для Row Level Security и контролируемого доступа к данным.
SECURITY DEFINER без явного `SET search_path` - уязвимость: злоумышленник может создать объект с тем же именем в своём search_path и перехватить вызов. Всегда добавляй `SET search_path = public, pg_temp` или более строгий вариант.
Функция создана с SECURITY DEFINER суперпользователем. Какие права используются при её вызове обычным пользователем?
Volatility: IMMUTABLE, STABLE, VOLATILE
Категория волатильности говорит оптимизатору PostgreSQL, можно ли кэшировать результат функции и использовать её в определённых контекстах. Неправильно указанная волатильность - источник тонких багов или потери производительности.
| Категория | Значение | Кэширование | Примеры |
|---|---|---|---|
| IMMUTABLE | Всегда один результат для одних аргументов, без обращения к БД | Агрессивное (pre-evaluation) | lower(), round(), md5() |
| STABLE | Один результат в рамках одного запроса, может читать БД | В рамках запроса | now(), current_setting(), функции с SELECT |
| VOLATILE | Результат может меняться каждый вызов (default) | Нет кэширования | random(), nextval(), INSERT/UPDATE внутри |
Все функции, читающие данные из таблиц, должны быть VOLATILE, иначе данные могут быть устаревшими.
STABLE корректен для функций, читающих данные - PostgreSQL гарантирует что в рамках одного запроса данные не изменятся (snapshot isolation). VOLATILE нужен только при записи данных или использовании функций типа `random()`.
PostgreSQL использует MVCC: каждый запрос видит консистентный снапшот данных. Внутри одного запроса результат SELECT не изменится. STABLE позволяет оптимизатору избежать повторных SELECT при многократном вызове с одними аргументами - это может давать заметный прирост производительности.
Функция делает SELECT из таблицы users и возвращает имя пользователя по ID. Какую категорию волатильности следует указать?
Итоги
- **Язык функции:** `LANGUAGE sql` для простых запросов (инлайнится оптимизатором), `LANGUAGE plpgsql` для условий, циклов и обработки исключений
- **SECURITY DEFINER:** выполнение с правами создателя; всегда добавляй `SET search_path` для защиты от path-injection; используй для RLS и контролируемого API
- **Волатильность:** IMMUTABLE (чистая функция, можно в индексе) > STABLE (читает БД, кэш в запросе) > VOLATILE (пишет/random, без кэша)
Связанные темы
Функции и триггеры взаимодействуют с ключевыми механизмами PostgreSQL:
- Row Level Security — SECURITY DEFINER-функции - стандартный способ реализации RLS API; функции скрывают сложность политик от клиентов
- Расширения: pg_cron — pg_cron вызывает SQL-функции и процедуры по расписанию; функции - основной способ инкапсулировать периодические задачи
- Транзакции и MVCC — Волатильность STABLE основана на MVCC-снапшотах; понимание снапшот-изоляции объясняет почему STABLE корректен для читающих функций
Вопросы для размышления
- Есть функция IMMUTABLE, которая обращается к таблице конфигурации через SELECT. Что произойдёт если данные в таблице изменятся? Почему PostgreSQL это позволяет?
- Триггер AFTER INSERT на таблице orders записывает в audit_log. INSERT в orders завершился ошибкой и транзакция откатилась. Запись в audit_log сохранится?
- Функция get_discount() вызывается 1000 раз в одном запросе с одинаковым аргументом. Какая категория волатильности позволит PostgreSQL избежать 999 лишних вызовов?