PostgreSQL trigger consistency check

Introduction

In this article, we are going to see how to implement a non-trivial consistency check using a PostgreSQL INSERT and UPDATE trigger.

By using a database trigger after executing an INSERT or UPDATE, we can ensure that the sum of salaries in a given department does not exceed the maximum budget allocated for the given department.

Domain Model

We are going to reuse the department and employee database tables from the article showing the difference between 2PL (Two-Phase Locking) and MVCC (Multi-Version Concurrency Control) when it comes to handling theWrite Skew anomaly:

The department is the parent table while the employee is the child table. Employees have a salary column, and the sum of salaries in a given department should not exceed the budget column value of the associated department table record.

Consistency check

PostgreSQL supports the SQL standard CHECK constraints , and we used them for the SINGLE_TABLE JPA inheritance strategy .

However, CHECK constraints are limited to the columns of the table for which we defined the custom constraint. If we want to implement a more complex data integrity rule, then a database trigger is a much more suitable alternative.

Therefore, we are going to create the following check_department_budget trigger function, which verifies that the sum of salaries in a given department does not exceed the allocated 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;

Notice that the check_department_budget PostgreSQL function returns a TRIGGER object since we want this trigger function to be executed in the context of table row INSERT or UPDATE events.

Now, we also need to define a PostgreSQL trigger that is executed after each INSERT or UPDATE on the employee table:

CREATE TRIGGER check_department_budget_trigger
AFTER INSERT OR UPDATE ON employee 
FOR EACH ROW EXECUTE PROCEDURE check_department_budget();

And, that’s it. We now have a trigger in place that, on every employee table INSERT or UPDATE, checks whether the sum of salaries does not exceed the department budget.

Testing time

Assuming we have the following IT department with a budget of 100000 :

| id | budget | name |
|----|--------|------|
| 1  | 100000 | IT   |

And, we have three employees currently working in the IT department:

| id | name  | salary | department_id |
|----|-------|--------|---------------|
| 1  | Alice | 40000  | 1             |
| 2  | Bob   | 30000  | 1             |
| 3  | Carol | 20000  | 1             |

Notice that the current sum of salaries is 90000 , so, currently, we are 10000 under budget.

Now, let’s consider that Alice and Bob want to run the following operations:

• Alice wants to give a year-end raise of 10% to all employees in the IT department, which should raise the budget from 90000 to 99000
• Bob wants to hire Dave for 9000 , therefore raising the budget from 90000 to 99000

If both Alice and Bob commit their transactions, we risk going over the budget. However, thanks to the check_department_budget trigger function, one of the transaction will be rolled back, as illustrated by the following diagram:

When Bob hires Dave, the budget was 90000 , so his INSERT statement is validated by the check_department_budget trigger function.

However, when Alice wants to execute the UPDATE, the budget is now 99000 , so, if the UPDATE succeeds, the new budget value will be 108900 . Luckily, the check_department_budget trigger function will not validate the UPDATE statement, and an exception will be thrown, and Alice’s transaction will be rolled back.

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Database trigger functions are very useful when it comes to applying consistency rules that involve multiple tables.

Many times, application developers try to enforce these rules in the application layer by using a read-modify-write data access pattern. However, on the default Read Committed isolation level, reading the sum of salaries in the application does not guarantee that the sum will be the same at the end of the transaction. So, without adding an extra pessimistic or optimistic locking mechanism, the read-modify-write will just lower the probability of a data integrity issue, without really eliminating it.

So, adding the data integrity rules at the database level is the best approach, as, in case of a constraint violation, the current running transaction will be rolled back, and the databases will never be left in an inconsistent state.