Триггеры - важный и удобный элемент в наборе инструментов систем управления баз данных.
Про триггеры привычно рассуждать в контексте реляционных баз данных, но и в NoSql-базах они тоже есть: в MongoDb, Neo4j, а в Redis есть Keyspace Notifications.
В дальнейшем разговор пойдет в основном о Postgresql. Best Practice для других баз могут отличаться.
Под триггером принято понимать специально оформленную функцию, автоматически запущенную СУБД по некоторому событию.
В реляционных базах триггеры связаны с событиями модификации данных - INSERT, UPDATE, DELETE, а в MongoDb есть еще и события на аутентификацию и события по расписанию.
Чтобы функция была вызвана автоматически, она должна быть выражена в виде хранимой процедуры, чтобы была возможность запустить ее в контексте сервера базы данных, в котором произошло целевое событие.
Немного отвлечемся и поговорим про организацию бизнес-логики в приложениях с базами данных. Есть три подхода, при которых бизнес-логика реализуется:
- В базе данных в хранимых процедурах
- В приложении, а база используется в качестве хранилища данных разной степени глупости
- Размазана по базе данных и приложению
Первый подход довольно часто практикуется при эксплуатации ORACLE и MSSQL. Так можно сделать и в Postgresql, особенно учитывая богатый набор языков для реализации хранимых процедур.
У этого подхода есть два узких места - хранимые процедуры очень сложно тестировать и при увеличении количества данных СУБД должна уметь поддерживать работу хранимых процедур поверх данных, распределенных по нескольким физическим серверам. Postgresql и Mysql такой возможностью похвастаться не могут, поэтому если вы разрабатываете проект с этими базами, постарайтесь избежать хранения бизнес-логики в СУБД.
Так же отдельной болью будет версионирование хранимых процедур и связанные с этим бэкапы.
Отдельную проблему, которую придется решать, это взаимодействие с внешними сервисами из базы (отправить письмо, сходить в процессинг, сохранить файл).
К плюсам можно отнести скорость выполнения хранимых процедур. Операции, выполненные рядом с данными будут кратно быстрее этих же операций, выполненных в приложении. Основная причина - network issues.
Кроме того, данный подход позволяет соединить СУБД и HTTP-сервер в одну сущность, используя, например, PostgRest.
При втором подходе в СУБД нет никаких хранимых процедур, и основной задачей базы в данном случае является консистентно сохранить данные. Эта задача реализуется декларативной настройкой ограничений (constraints), таких, как ограничения на NOT NULL, внешними ключами, CHECK-правилами и DOMAIN-типами. Кроме того, процессы бизнес-логики со стороны приложения должны быть оформлены в транзакции, чтобы избежать проблем с консистентностью.
К плюсам можно отнести хорошую тестируемость, потенциальную возможность шардирования (зависит от схемы, не всегда получится сохранить консистентность), удобное версионирование на стороне приложения, понятные миграции и бэкапы.
В прошлом пункте к плюсам хранимых процедур была отнесена скорость работы. Операции в приложении всегда будет работать медленнее, чем операции в базе, но можно найти баланс. Если в пределах одного процесса бизнес-логики нам требуется прочитать-обновить-сохранить данные без взаимодействия с внешними сервисами, нам на помощь придут Common Table Expressions. Это несколько SQL-операторов, которые могут быть выполнены последовательно, и результат работы одного может быть источником данных для другого. Все эти операторы выполняются в пределах одной транзакции, и дают возможность получить скорость работы сравнимой со скоростью хранимой процедуры. У этого подхода есть важное ограничение - воспользоваться можно только SQL-синтаксисом.
Третий подход, на мой взгляд, является самым безответственным и встречается у неопытных архитекторов или когда задачами архитектора занимаются милд-инженеры, умеющие все понемногу. В нем проблемы первого подхода возводятся в степень, поэтому старайтесь избегать такой архитектуры.
Есть шутка что большие проекты, обычно, находятся в двух состояниях - переписывания всего кода приложения на процедуры или переписывания всех процедур в код приложения :)
В своей работе я призываю придерживаться второго подхода - реализации бизнес логики в приложении, и дальнейший рассказ про триггеры я будет именно с этой позиции.
Итак, события генерируются на INSERT, UPDATE и DELETE до (BEFORE) и после (AFTER) соответствующей операции.
Как правило, функции, запускающиеся до (BEFORE) операции используются для валидации данных. Если по какой-то причине при проектировании вам не хватило констрейнтов и прав на таблицы, то в функции, запускаемой до изменения данных можно провалидировать операцию и прервать ее при необходимости.
Пример №1.
Предположим, что вы делаете подсистему обработки платежей и есть задача ограничить платежи в определенной валюте в зависимости от страны банка, выдавшего карту.
CREATE TABLE payments (
...
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
...
);
CREATE TABLE currency2country ( ...
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
...
);
Перед вставкой в таблицу payments можно сделать триггер, в котором проверить существует ли запись в таблице currency2country, и если нет, прервать вставку.
CREATE OR REPLACE FUNCTION payments_before_insert_update() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT * FROM currency2country WHERE bank_country_id = NEW.bank_country_id AND currency_id = NEW.currency_id ) THEN
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Это плохой триггер.
Плохой он по той причине, что бизнес-логика здесь вынесена в хранимую процедуру и выражена императивно. И, вроде, ничего страшного. Но теперь представьте себя на месте коллеги, который только пришел в проект и не может понять что происходит со вставкой данных, когда никаких ограничений не описано в схеме базы данных.
В данном случае лучше поступить следующим образом:
CREATE TABLE currency2country ( ...
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
UNIQUE (bank_country_id, currency_id),
... );
CREATE TABLE payments ( ...
bank_country_id SMALLINT NOT NULL,
currency_id SMALLINT NOT NULL,
FOREIGN KEY (bank_country_id, currency_id) REFERENCES currency2country (bank_country_id, currency_id),
... );
Результат тот же, но никаких триггеров и процедур.
Пример №2.
Так же триггер может применяться для валидации enum-полей, в которых предполагается некоторая последовательность изменения значений. Например, статус платежа может меняться new->finished/canceled/error, но никогда не может смениться с error на finished. Вот как это сделано в одной из реализаций Finite-State Machine на триггерах. Это довольно мудреный подход, ценность которого придется донести до всех участников команды разработки.
Можно ли решить эту проблему без триггеров?
Первый вариант подойдет, если flow изменений линеен (как в примере выше) и не предполагается, что он может быть изменен. Мы можем учесть порядок смены статусов в запросе:
UPDATE payment SET status = 'finished' WHERE status<'finished' ....
У enum-типа в Postgresql есть свойство, о котором не все знают: значения enum-типа сортируются по порядку их объявлений. Поэтому
CREATE TYPE payment_status AS ENUM ('new','finished','canceled','error');
SELECT
'new' :: payment_status < 'finished' :: payment_status,
'canceled' :: payment_status < 'finished' :: payment_status
;
вернет true, false;
Таким образом мы можем быть уверены, что статус платежа не сменится на "предыдущий".
Второй вариант подойдет в том случае, если смена статусом может быть нелинейной. Подход близок к Finite State Machine, указанному выше, но без триггеров. Необходимо создать функцию check_next_payment_status, которая знает о всех возможных последовательностях изменения статусов и принимает в качестве параметров текущий статус и новый, а возвращает true/false.
Тогда запрос будет выглядеть так:
UPDATE payment SET status = 'finished' WHERE check_next_payment_status(status,'finished') ....
Третий вариант - перечислить все возможные варианты смены статуса в самом запросе:
UPDATE payment SET status = 'finished' WHERE status IN ('new', ...) ....
Четвертый вариант - иметь 2 поля - status и status_previous и всю логику смены перенести в CHECK таблицы:
CREATE TABLE payments ( ...
status payment_status NOT NULL,
status_previous payment_status NOT NULL,
CHECK (
(status = 'finished' AND status_previous = 'new')
OR
(status = 'error' AND status_previous = 'new')
...
)
...
);
А обновление делать так:
UPDATE payment SET status_previous = status, status = 'finished' ....
"Так ведь разработчик рано или поздно забудет про такой подход, а новый сотрудник может этого никогда и не узнать. Да и вообще, выглядит это как-то так...", - можете сказать вы. И будете правы!
Но скрытие логики в триггерах или в более навернутых конечных автоматах может привести к некому аналогу неуточняемого поведения. Что будет, если один из разработчиков поменяет flow внутри триггера? Прекрасно, если существуют тесты, покрывающие все переходы. А если таких тестов нет или они неполны?
К сожалению, в Postgresql нет встроенного декларативно описываемого типа с конечными автоматами. Поэтому наиболее безопасным мне кажется третий вариант - перечислить все возможные варианты смены статуса в самом запросе.
Такой вариант можно надежно проверить статическим анализатором и быть уверенным в том, что все кейсы покрыты, даже если enum-тип поменяется со временем.
Но все перечисленные варианты выражены явно, что хорошо скажется на поддерживаемости кода.
Пример №3.
Еще распространенный вариант использования BEFORE-триггеров - это реализация в Postgresql функции UPSERT. Эта функция вставляет данные, но если данные по такому первичному ключу уже существуют, они заменяются на новые. В Postgresql нет такой функции и ее раньше часто реализовывали через триггер вида:
CREATE OR REPLACE FUNCTION payments_before_insert() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT * FROM payments WHERE bank_country_id = NEW.bank_country_id ) THEN
UPDATE payments SET bank_country_id = NEW.bank_country_id, counter = counter+NEW.counter WHERE bank_country_id = NEW.bank_country_id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Если вы обновляли Posgresql хотя бы раз за последние 4 года, то знайте, что начиная с версии 9.5 появился механизм ON CONFLICT DO UPDATE, который позволяет больше так не делать :)
Если вы пришли к тому, что вам необходим BEFORE-триггер, подумайте хорошенько. Возможно, вам стоит пересмотреть архитектуру базы.
В своей работе я никогда не использую BEFORE-триггеры. И вам не рекомендую :)
А что же с AFTER-триггерами?
Им можно дать небольшое послабление :)
Поначалу кажется, что AFTER-триггеры не могут повлиять на бизнес-логику. Но нет, могут. Любой тип триггрера может иметь side-эффекты, или иными словами, может изменить данные в любой таблице, не только в целевой.
Бывает, что в AFTER-триггере разработчик обновляет статусы в связанной таблице. Например, платеж переходит в статус finished, и заказ, к которому он привязан должен перейти в статус paid. И да, нам хотелось бы сделать это в пределах одной транзакции.
Сделайте это в Common Table Expressions! Эта логика будет всегда под рукой и всегда лежать рядом с кодом приложения.
Представьте, что требования были такими, что к одному заказу всегда привязывался только один платеж. А потом они поменялись. Оплатить можно несколькими способами и статус 'paid' должен выставится только после завершения ВСЕХ платежей. И если эта логика будет лежать в самом запросе, вы сэкономите много бессонных ночей себе и денег компании.
Еще в AFTER-триггерах некоторые очень любят обновлять накопительную статистику. Например, количество платежей и сумму по клиенту в отдельной аггрегацционной таблице. Та же история - все в Common Table Expressions!
Но есть еще и отъявленные бандиты!
Это триггеры, в которых делают селекты с агрегатами, для того чтобы потом обновить накопительную статистику.
Это триггеры, в которых используют рекомендательные блокировки.
Это триггеры, в которых могут вызвать внешний сервис для отправки писем.
Это триггеры, в которых могут вызвать внешний источник данных через fdw.
Это злой триггер.
"Какой-то дикий запад! Если тут приличные люди триггеры?"
Да, есть несколько заповедей хорошего триггера.
- это AFTER-триггер
- внутри триггера не делаются селекты
- внутри триггера не делаются апдейты
- внутри триггера не делаются запросы за пределы сервера
- внутри триггера не используются рекомендательные блокировки
"И зачем нужен такой триггер? Он ничего не умеет"
Умеет. Хороший триггер оперирует только данными, полученными в объектах OLD и NEW.
"И что он может с этим сделать?"
Из триггера можно сохранить лог.
Вернемся к примеру нелинейного изменения статусов. Обновляя статус в таблице вы теряете исторические данные о всех предыдущих значениях статуса. Иметь историю изменений очень полезно для последующего анализа работы системы - задержек между сменой статусов, путей попадания в нестандартные ситуации. Можно сохранять разницу между предыдущим и текущим значением числового поля.
Создаем отдельную таблицу и пишем туда информацию из основной.
Для того чтобы не сильно страдать с различными структурами таблиц, можно сделать все структуры похожими - идентификатор из основной таблицы, дата создания записи и JSON-объект отображающий всю записываемую строку.
"Лог? И это все?"
Все. Если ваша бизнес-логика находится в приложении, то это все.