Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как реализовать нетривиальную проверку согласованности с помощью триггера ВСТАВКИ и ОБНОВЛЕНИЯ PostgreSQL.
Используя триггер базы данных после выполнения ВСТАВКИ или ОБНОВЛЕНИЯ, мы можем гарантировать, что сумма зарплат в данном отделе не превысит максимальный бюджет, выделенный для данного отдела.
Модель предметной области
Мы собираемся повторно использовать отдел
и сотрудник
таблицы базы данных из статьи, показывающие разницу между 2PL (Двухфазная блокировка) и MVCC (Управление параллелизмом нескольких версий) когда дело доходит до обработки аномалии Перекос записи :
Отдел
является родительской таблицей, в то время как сотрудник является дочерней таблицей. Сотрудники имеют столбец зарплата
, и сумма зарплат в данном отделе не должна превышать значение столбца бюджет
соответствующей записи отдела
таблицы.
Проверка согласованности
PostgreSQL поддерживает стандарт SQL ПРОВЕРКА
ограничения , и мы использовали их для стратегии наследования SINGLE_TABLE JPA .
Однако ограничения CHECK
ограничены столбцами таблицы, для которых мы определили пользовательское ограничение. Если мы хотим реализовать более сложное правило целостности данных, то триггер базы данных является гораздо более подходящей альтернативой.
Поэтому мы собираемся создать следующую check_department_budget
триггерную функцию, которая проверяет, что сумма зарплат в данном отделе не превышает выделенный бюджет.
CREATE OR REPLACE FUNCTION check_department_budget() RETURNS TRIGGER AS $$ DECLARE allowed_budget BIGINT; new_budget BIGINT; BEGIN SELECT INTO allowed_budget budget FROM department WHERE id = NEW.department_id; SELECT INTO new_budget SUM(salary) FROM employee WHERE department_id = NEW.department_id; IF new_budget > allowed_budget THEN RAISE EXCEPTION 'Overbudget department [id:%] by [%]', NEW.department_id, (new_budget - allowed_budget); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Обратите внимание, что функция check_department_budget
PostgreSQL возвращает объект TRIGGER
, поскольку мы хотим, чтобы эта функция триггера выполнялась в контексте событий ВСТАВКИ или ОБНОВЛЕНИЯ строк таблицы.
Теперь нам также нужно определить триггер PostgreSQL, который выполняется после каждой ВСТАВКИ или ОБНОВЛЕНИЯ в таблице сотрудник
:
CREATE TRIGGER check_department_budget_trigger AFTER INSERT OR UPDATE ON employee FOR EACH ROW EXECUTE PROCEDURE check_department_budget();
И это все. Теперь у нас есть триггер, который при каждой ВСТАВКЕ или ОБНОВЛЕНИИ таблицы сотрудник
проверяет, не превышает ли сумма заработной платы бюджет отдела.
Время тестирования
Предполагая, что у нас есть следующий ИТ-отдел с бюджетом в 100000
:
| id | budget | name | |----|--------|------| | 1 | 100000 | IT |
И у нас есть три сотрудника, которые в настоящее время работают в ИТ-отделе:
| id | name | salary | department_id | |----|-------|--------|---------------| | 1 | Alice | 40000 | 1 | | 2 | Bob | 30000 | 1 | | 3 | Carol | 20000 | 1 |
Обратите внимание, что текущая сумма заработной платы составляет 90000
, таким образом, в настоящее время мы 10000
в рамках бюджета.
Теперь давайте рассмотрим, что Алиса и Боб хотят выполнить следующие операции:
- Алиса хочет повысить зарплату на конец года на 10% всем сотрудникам ИТ-отдела, что должно увеличить бюджет с
90000
до99000
- Боб хочет нанять
Дейва
для9000
, следовательно, увеличение бюджета с90000
до99000
Если и Алиса, и Боб совершат свои транзакции, мы рискуем превысить бюджет. Однако благодаря функции check_department_budget
триггера одна из транзакций будет откатана, как показано на следующей диаграмме:
Когда Боб нанимал Дейва, бюджет был 90000
, поэтому его оператор INSERT проверяется функцией check_department_budget
триггера.
Однако, когда Алиса хочет выполнить ОБНОВЛЕНИЕ, бюджет теперь 99000
, таким образом, если ОБНОВЛЕНИЕ пройдет успешно, новое значение бюджета будет 108900
. К счастью, функция check_department_budget
триггера не проверит инструкцию UPDATE, и будет выдано исключение, а транзакция Алисы будет откатана.
Круто, правда?
Вывод
Функции запуска базы данных очень полезны, когда дело доходит до применения правил согласованности, которые включают несколько таблиц.
Много раз разработчики приложений пытаются обеспечить соблюдение этих правил на уровне приложений, используя шаблон доступа к данным для чтения, изменения и записи. Однако на уровне изоляции, установленном по умолчанию для чтения, чтение суммы заработной платы в приложении не гарантирует, что сумма будет такой же в конце транзакции. Таким образом, без добавления дополнительного пессимистичного или оптимистичного механизма блокировки функция чтения-изменения-записи просто снизит вероятность проблемы целостности данных, не устраняя ее на самом деле.
Таким образом, добавление правил целостности данных на уровне базы данных является наилучшим подходом, так как в случае нарушения ограничений текущая выполняемая транзакция будет откатана, и базы данных никогда не останутся в несогласованном состоянии.