Рубрики
Без рубрики

Проверка согласованности триггера PostgreSQL

Узнайте, как реализовать нетривиальную проверку согласованности с помощью триггера ВСТАВКИ и ОБНОВЛЕНИЯ PostgreSQL, который обеспечивает выполнение данного правила проверки целостности данных.

Автор оригинала: 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, и будет выдано исключение, а транзакция Алисы будет откатана.

Круто, правда?

Вывод

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

Много раз разработчики приложений пытаются обеспечить соблюдение этих правил на уровне приложений, используя шаблон доступа к данным для чтения, изменения и записи. Однако на уровне изоляции, установленном по умолчанию для чтения, чтение суммы заработной платы в приложении не гарантирует, что сумма будет такой же в конце транзакции. Таким образом, без добавления дополнительного пессимистичного или оптимистичного механизма блокировки функция чтения-изменения-записи просто снизит вероятность проблемы целостности данных, не устраняя ее на самом деле.

Таким образом, добавление правил целостности данных на уровне базы данных является наилучшим подходом, так как в случае нарушения ограничений текущая выполняемая транзакция будет откатана, и базы данных никогда не останутся в несогласованном состоянии.