In PostgreSQL, what does CREATE CONSTRAINT TRIGGER with DEFERRABLE INITIALLY DEFERRED change about an AFTER trigger, and which role runs it in PostgreSQL 18?
answer
- AFTER ROW, timing adjustable
- fires at commit when deferred
- SET CONSTRAINTS uses its name
- 18: role at queue time
basics
~20 sA constraint trigger is an AFTER ROW trigger whose firing can be deferred to commit and controlled by SET CONSTRAINTS. Since PostgreSQL 18 every AFTER trigger runs as the role active when its event was queued, unless the function is SECURITY DEFINER.
solid answer
~50 s`CREATE CONSTRAINT TRIGGER` makes an ordinary trigger whose timing can be adjusted with `SET CONSTRAINTS`, using the trigger's name. It must be `AFTER ... FOR EACH ROW` on a plain table. With `DEFERRABLE INITIALLY DEFERRED` its queued events fire at commit instead of at the end of the statement, so it can check invariants that are only true once several statements have run, such as transfer legs netting to zero, which a `CHECK` constraint cannot do because CHECK is not deferrable. If it raises an error at commit, `COMMIT` fails and the transaction rolls back. Its `WHEN` clause is still evaluated immediately. In PostgreSQL 18, AFTER triggers run as the role that was active when the event was queued; through 17 they ran as whatever role was active when they fired, for example at `COMMIT` after a `RESET ROLE`. A `SECURITY DEFINER` function still runs as its owner.
code
sql · 18 linesCREATE FUNCTION transfer_nets_to_zero() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF (SELECT sum(qty_delta) FROM stock_movement
WHERE transfer_id = NEW.transfer_id) <> 0 THEN
RAISE EXCEPTION 'transfer % does not net to zero',
NEW.transfer_id;
END IF;
RETURN NULL;
END;
$$;
CREATE CONSTRAINT TRIGGER transfer_balanced
AFTER INSERT ON stock_movement
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN (NEW.transfer_id IS NOT NULL)
EXECUTE FUNCTION transfer_nets_to_zero();go deeper
Recall that CREATE CONSTRAINT TRIGGER makes an AFTER ROW trigger that can be deferred until the transaction commits.
Explain the four timing combinations, SET CONSTRAINTS by trigger name, and why CHECK constraints cannot replace a deferred trigger.
Know the edges: WHEN evaluated immediately, enable state checked at queue time, COMMIT failing and rolling back, no OR REPLACE, and the 18 change to the executing role and what it breaks on upgrade.
Decide which invariants belong in deferred triggers versus declarative constraints or application workflows, given that trigger checks are not race-free and depend on role design.
## What CONSTRAINT adds to a trigger A **constraint trigger** is created with `CREATE CONSTRAINT TRIGGER` and is the same as a regular trigger except that **when it fires can be adjusted**, like a deferrable constraint. PostgreSQL itself implements foreign keys, and deferrable unique and exclusion constraints, with internally generated constraint triggers; the user-facing form lets you write your own deferrable checks. Its rules: - It must be an `AFTER ... FOR EACH ROW` trigger on a plain table, not a foreign table. - It accepts `DEFERRABLE` or `NOT DEFERRABLE` and `INITIALLY IMMEDIATE` or `INITIALLY DEFERRED`. - Its name is the name `SET CONSTRAINTS` uses to change its timing inside a transaction. - It is expected to raise an exception when the rule it implements is violated. ## When it fires | Declaration | Fires | |---|---| | ordinary AFTER ROW trigger | at the end of the statement | | constraint trigger, `NOT DEFERRABLE` | at the end of the statement, like an ordinary one | | `DEFERRABLE INITIALLY IMMEDIATE` | end of statement, unless `SET CONSTRAINTS name DEFERRED` postpones it in this transaction | | `DEFERRABLE INITIALLY DEFERRED` | at commit, unless `SET CONSTRAINTS name IMMEDIATE` forces pending events to fire now | If a deferred trigger raises an error at commit, the `COMMIT` reports that error and the whole transaction is rolled back. Two details trip people up. The `WHEN` condition is **not** deferred: it is evaluated right after the row change, and if it is false nothing is queued. And whether a trigger is enabled is checked when the event is queued, not when the deferred function finally runs. ## A cross-row invariant in a warehouse An inter-warehouse transfer writes two rows to `stock_movement`, one negative and one positive, often in separate statements. The rule that a transfer nets to zero is false between those statements, so it must be checked at commit: 1. Write a function `RETURNS trigger` that sums `qty_delta` for `NEW.transfer_id` and raises an exception if the sum is not zero. 2. Create `CONSTRAINT TRIGGER ... AFTER INSERT ON stock_movement DEFERRABLE INITIALLY DEFERRED FOR EACH ROW` with `WHEN (NEW.transfer_id IS NOT NULL)`. 3. Let both legs be inserted; at commit the trigger runs once per queued row and sees the complete transfer. A `CHECK` constraint cannot do this: in PostgreSQL only `UNIQUE`, `PRIMARY KEY`, `EXCLUDE` and foreign-key constraints are deferrable, and `CHECK` cannot look at other rows anyway. A trigger-based check is not race-free the way a real constraint is, since concurrent transactions do not see each other's uncommitted rows, so it fits invariants scoped to the transaction's own data. ## Limits - No `OR REPLACE`: replace a constraint trigger by dropping and recreating it. - No transition tables, which keeps deferred events row-shaped. - No statement level and no BEFORE timing. - The `FROM referenced_table` clause exists for foreign-key internals and is not recommended for general use. ## PostgreSQL 18: whose privileges the trigger uses PostgreSQL 18 changed which role executes AFTER triggers. They now run as **the role that was active when the trigger event was queued**. Through 17 they ran as the role active when they actually executed, which for a deferred trigger is the role current at `COMMIT`. The difference appears when a session changes role between the write and the commit: - A pooled connection runs `SET ROLE clerk`, inserts movements, then `RESET ROLE` before `COMMIT`. In 18 the deferred trigger runs as `clerk`; in 17 it ran as the login role. - A write made inside a `SECURITY DEFINER` function queues its events under the definer's identity, and in 18 the AFTER trigger keeps that identity. - A trigger function that is itself `SECURITY DEFINER` runs as the function's owner in every version. When upgrading, review AFTER and deferred triggers whose logic reads `current_user` or depends on the executing role's privileges, because a check that passed at commit under a privileged role may now fail under the role that made the change.
- How do you make the deferred check run before COMMIT, for example to report the error to the user mid-transaction?Run `SET CONSTRAINTS transfer_balanced IMMEDIATE` (or `SET CONSTRAINTS ALL IMMEDIATE`). Pending deferred events for that trigger fire at once, and from then on in the transaction it fires at statement end. An error is raised by the `SET CONSTRAINTS` statement itself, so the application can catch it before attempting `COMMIT`.
- After upgrading to PostgreSQL 18, a deferred trigger that writes to a restricted table starts failing with permission errors. Why?In 18 AFTER triggers run as the role that was active when the event was queued. If the application did `SET ROLE` to a less privileged role for the write and reset it before `COMMIT`, the trigger used to run as the privileged role and now runs as the restricted one. Grant that role the needed privilege, or make the trigger function `SECURITY DEFINER` with a pinned `search_path`.
- Any AFTER trigger can be declared DEFERRABLE without the CONSTRAINT keyword.
- A deferred constraint trigger evaluates its WHEN condition at commit time.
- A failing deferred check at commit rolls back only the offending row.
- A CHECK constraint can be made DEFERRABLE to validate cross-statement rules.
- In PostgreSQL 18 deferred AFTER triggers still run as the role current at COMMIT.
Assumes PostgreSQL 18; from 18 AFTER triggers run as the role that queued the event, while 17 and earlier used the role active when the trigger fired.