Triggers to enforce constraints & how to write them correctly

Motörhead sings about deferred constraint triggers — © Laurenz Albe 2019

Table of Contents

Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for.

It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”.

A test case

Suppose we have a table of prisons and a table of prison guards:

CREATE SCHEMA jail_app;

CREATE TABLE jail_app.prison (
   prison_id   integer PRIMARY KEY,
   prison_name text    NOT NULL
);

INSERT INTO jail_app.prison (prison_id, prison_name) VALUES
   (1, 'Karlau'),
   (2, 'Stein');

CREATE TABLE jail_app.guard (
   guard_id   integer PRIMARY KEY,
   guard_name text    NOT NULL
);

INSERT INTO jail_app.guard (guard_id, guard_name) VALUES
   (41, 'Alice'),
   (42, 'Bob'),
   (43, 'Chris');

CREATE SCHEMA jail_app;

CREATE TABLE jail_app.prison (

prison_id integer PRIMARY KEY,

prison_name text NOT NULL

);

INSERT INTO jail_app.prison (prison_id, prison_name) VALUES

(1, 'Karlau'),

(2, 'Stein');

CREATE TABLE jail_app.guard (

guard_id integer PRIMARY KEY,

guard_name text NOT NULL

);

INSERT INTO jail_app.guard (guard_id, guard_name) VALUES

(41, 'Alice'),

(42, 'Bob'),

(43, 'Chris');

Then we have a junction table that stores which guard is on duty in which prison:

CREATE TABLE jail_app.on_duty (
   prison_id integer REFERENCES prison,
   guard_id  integer REFERENCES guard,
   PRIMARY KEY (prison_id, guard_id)
);

INSERT INTO jail_app.on_duty (prison_id, guard_id) VALUES
   (1, 41), (2, 42), (2, 43);

CREATE TABLE jail_app.on_duty (

prison_id integer REFERENCES prison,

guard_id integer REFERENCES guard,

PRIMARY KEY (prison_id, guard_id)

);

INSERT INTO jail_app.on_duty (prison_id, guard_id) VALUES

(1, 41), (2, 42), (2, 43);

So, Alice is on duty in Karlau, and Bob and Chris are on duty in Stein.

Naïve implementation of a constraint as trigger

As guards go on and off duty, rows are added to and deleted from on_duty. We want to establish a constraint that at least one guard has to be on duty in any given prison.

Unfortunately there is no way to write this as a normal database constraint (if you are tempted to write a CHECK constraint that counts the rows in the table, think again).

But it would be easy to write a BEFORE DELETE trigger that ensures the condition:

CREATE FUNCTION jail_app.checkout_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF (SELECT count(*)
       FROM jail_app.on_duty
       WHERE prison_id = OLD.prison_id
      ) < 2
   THEN
      RAISE EXCEPTION 'sorry, you are the only guard on duty';
   END IF;

   RETURN OLD;
END;$$;

CREATE TRIGGER checkout_trig BEFORE DELETE ON jail_app.on_duty
   FOR EACH ROW EXECUTE PROCEDURE jail_app.checkout_trig();

CREATE FUNCTION jail_app.checkout_trig() RETURNS trigger

LANGUAGE plpgsql AS

$$BEGIN

IF (SELECT count(*)

FROM jail_app.on_duty

WHERE prison_id = OLD.prison_id

) < 2

THEN

RAISE EXCEPTION 'sorry, you are the only guard on duty';

END IF;

RETURN OLD;

END;$$;

CREATE TRIGGER checkout_trig BEFORE DELETE ON jail_app.on_duty

FOR EACH ROW EXECUTE PROCEDURE jail_app.checkout_trig();

But, as we will see in the next section, we made a crucial mistake here.

What is wrong with our trigger constraint?

Imagine Bob wants to go off duty.

The prison guard application runs a transaction like the following:

START TRANSACTION;

DELETE FROM jail_app.on_duty
WHERE guard_id = (SELECT guard_id
                  FROM jail_app.guard
                  WHERE guard_name = 'Bob');

COMMIT;

START TRANSACTION;

DELETE FROM jail_app.on_duty

WHERE guard_id = (SELECT guard_id

FROM jail_app.guard

WHERE guard_name = 'Bob');

COMMIT;

Now if Chris happens to have the same idea at the same time, the following could happen (the highlighted lines form a second, concurrent transaction):

START TRANSACTION;

DELETE FROM jail_app.on_duty
WHERE guard_id = (SELECT guard_id
                  FROM jail_app.guard
                  WHERE guard_name = 'Bob');

          START TRANSACTION;

          DELETE FROM jail_app.on_duty
          WHERE guard_id = (SELECT guard_id
                            FROM jail_app.guard
                            WHERE guard_name = 'Chris');

          COMMIT;

COMMIT;

START TRANSACTION;

DELETE FROM jail_app.on_duty

WHERE guard_id = (SELECT guard_id

FROM jail_app.guard

WHERE guard_name = 'Bob');

START TRANSACTION;

DELETE FROM jail_app.on_duty

WHERE guard_id = (SELECT guard_id

FROM jail_app.guard

WHERE guard_name = 'Chris');

COMMIT;

Now the first transaction has not yet committed when the second UPDATE runs, so the trigger function running in the second transaction cannot see the effects of the first update. That means that the second transaction succeeds, both guards go off duty, and the prisoners can escape.

You may think that this is a rare occurrence and you can get by ignoring that race condition in your application. But don't forget there are bad people out there, and they may attack your application using exactly such a race condition (in the recent fad of picking impressive names for security flaws, this has been called an ACIDRain attack).

Do normal constraints have the same problem?

Given the above, you may wonder if regular constraints are subject to the same problem. After all, this is a consequence of PostgreSQL's multi-version concurrency control (MVCC).

When checking constraints, PostgreSQL also checks rows that would normally not be visible to the current transaction. This is against the normal MVCC rules but guarantees that constraints are not vulnerable to this race condition.

You could potentially do the same if you write a trigger function in C, but few people are ready to do that. With trigger functions written in any other language, you have no way to “peek” at uncommitted data.

Solving the problem with “pessimistic locking”

We can avoid the race condition by explicitly locking the rows we check. This effectively serializes data modifications, so it reduces concurrency and hence performance.

Don't consider locking the whole table, even if it seems a simpler solution.

Our trigger now becomes a little more complicated. We want to avoid deadlocks, so we will make sure that we always lock rows in the same order. For this we need a statement level trigger with a transition table (new since v10):

CREATE OR REPLACE FUNCTION jail_app.checkout_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF EXISTS (
         WITH remaining AS (
            /* of the prisons where somebody went off duty,
               select those which have a guard left */
            SELECT on_duty.prison_id
            FROM jail_app.on_duty
               JOIN deleted
                  ON on_duty.prison_id = deleted.prison_id
            ORDER BY on_duty.prison_id, on_duty.guard_id
            /* lock those remaining entries */
            FOR KEY SHARE OF on_duty
         )
         SELECT prison_id FROM deleted
         EXCEPT
         SELECT prison_id FROM remaining
      )
   THEN
      RAISE EXCEPTION 'cannot leave a prison without guards';
   END IF;

   RETURN NULL;
END;$$;

DROP TRIGGER IF EXISTS checkout_trig ON jail_app.on_duty;

CREATE TRIGGER checkout_trig AFTER DELETE ON jail_app.on_duty
   REFERENCING OLD TABLE AS deleted
   FOR EACH STATEMENT EXECUTE PROCEDURE jail_app.checkout_trig();

CREATE OR REPLACE FUNCTION jail_app.checkout_trig() RETURNS trigger

LANGUAGE plpgsql AS

$$BEGIN

IF EXISTS (

WITH remaining AS (

/* of the prisons where somebody went off duty,

select those which have a guard left */

SELECT on_duty.prison_id

FROM jail_app.on_duty

JOIN deleted

ON on_duty.prison_id = deleted.prison_id

ORDER BY on_duty.prison_id, on_duty.guard_id

/* lock those remaining entries */

FOR KEY SHARE OF on_duty

)

SELECT prison_id FROM deleted

EXCEPT

SELECT prison_id FROM remaining

)

THEN

RAISE EXCEPTION 'cannot leave a prison without guards';

END IF;

RETURN NULL;

END;$$;

DROP TRIGGER IF EXISTS checkout_trig ON jail_app.on_duty;

CREATE TRIGGER checkout_trig AFTER DELETE ON jail_app.on_duty

REFERENCING OLD TABLE AS deleted

FOR EACH STATEMENT EXECUTE PROCEDURE jail_app.checkout_trig();

This technique is called “pessimistic locking” since it expects that there will be concurrent transactions that “disturb” our processing. Such concurrent transactions are preemptively blocked. Pessimistic locking is a good strategy if conflicts are likely.

Solving the probem with “optimistic locking”

Different from pessimistic locking, “optimistic locking” does not actually lock the contended objects. Rather, it checks that no concurrent transaction has modified the data between the time we read them and the time we modify the database.

This improves concurrency, and we don't have to change our original trigger definition. The downside is that we must be ready to repeat a transaction that failed because of concurrent data modifications.

The most convenient way to implement optimistic locking is to raise the transaction isolation level. In our case, REPEATABLE READ is not enough to prevent inconsistencies, and we'll have to use SERIALIZABLE.

All transactions that access jail_app.on_duty must start like this:

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1	START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Then PostgreSQL will make sure that concurrent transactions won't succeed unless they are serializable. That means that the transactions can be ordered so that serial execution of the transactions in this order would produce the same result.

If PostgreSQL cannot guarantee this, it will terminate one of the transactions with

ERROR:  could not serialize access due to read/write dependencies
        among transactions
HINT:  The transaction might succeed if retried.

ERROR: could not serialize access due to read/write dependencies

among transactions

HINT: The transaction might succeed if retried.

This is a serialization error (SQLSTATE 40001) and doesn't mean that you did something wrong. Such errors are normal with isolation levels above READ COMMITTED and tell you to simply retry the transaction.

Optimistic locking is a good strategy if conflicts are expected to occur only rarely. Then you don't have to pay the price of repeating the transaction too often.

It should be noted that SERIALIZABLE comes with a certain performance hit. This is because PostgreSQL has to maintain additional “predicate locks”. See the documentation for performance considerations.

What about these “constraint triggers”?

Finally, PostgreSQL has the option to create “constraint triggers” with CREATE CONSTRAINT TRIGGER. It sounds like such triggers could be used to avoid the race condition.

Constraint triggers respect the MVCC rules, so they cannot “peek” at uncommitted rows of concurrent transactions. But the trigger execution can be deferred to the end of the transaction. They also have an entry in the pg_constraint system catalog.

Note that constraint triggers have to be AFTER triggers FOR EACH ROW, so we will have to rewrite the trigger function a little:

CREATE OR REPLACE FUNCTION jail_app.checkout_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   -- the deleted row is already gone in an AFTER trigger
   IF (SELECT count(*) FROM jail_app.on_duty
       WHERE prison_id = OLD.prison_id
      ) < 1
   THEN
      RAISE EXCEPTION 'sorry, you are the only guard on duty';
   END IF;

   RETURN OLD;
END;$$;

DROP TRIGGER IF EXISTS checkout_trig ON jail_app.on_duty;

CREATE CONSTRAINT TRIGGER checkout_trig
   AFTER DELETE ON jail_app.on_duty
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE jail_app.checkout_trig();

CREATE OR REPLACE FUNCTION jail_app.checkout_trig() RETURNS trigger

LANGUAGE plpgsql AS

$$BEGIN

-- the deleted row is already gone in an AFTER trigger

IF (SELECT count(*) FROM jail_app.on_duty

WHERE prison_id = OLD.prison_id

) < 1

THEN

RAISE EXCEPTION 'sorry, you are the only guard on duty';

END IF;

RETURN OLD;

END;$$;

DROP TRIGGER IF EXISTS checkout_trig ON jail_app.on_duty;

CREATE CONSTRAINT TRIGGER checkout_trig

AFTER DELETE ON jail_app.on_duty

DEFERRABLE INITIALLY DEFERRED

FOR EACH ROW EXECUTE PROCEDURE jail_app.checkout_trig();

By making the trigger INITIALLY DEFERRED, we tell PostgreSQL to check the condition at COMMIT time. This will reduce the window for the race condition a little, but the problem is still there. If concurrent transactions run the trigger function at the same time, they won't see each other's modifications.

If constraint triggers don't live up to the promise in their name, why do they have that name? The answer is in the history of PostgreSQL: CREATE CONSTRAINT TRIGGER was originally used “under the hood” to create database constraints. Even though that is no more the case, the name has stuck. “Deferrable trigger” would be a better description.