PostgreSQL trigger consistency check
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
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.
We are going to reuse the
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 the Write Skew anomaly:
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.
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_budgetPostgreSQL function returns a
TRIGGERobject 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
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.
Assuming we have the following IT department with a budget of
| 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
- Bob wants to hire
9000, therefore raising the budget from
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.
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.