2026-09-24 · 8 min read
- sql
- postgresql
- triggers
Transition relations expose the rows affected by one SQL statement to eligible AFTER statement-level triggers. OLD TABLE represents rows before supported changes, while NEW TABLE represents rows after them. This enables set-based audit, summary, and integrity workflows without invoking application code once per row.
The feature is not a general change-data-capture stream. Its visibility, eligibility, ordering, and transaction behavior follow PostgreSQL trigger semantics and the deployed server version.
Define the statement contract
Specify the table, event, timing, level, transition relation names, trigger order, function owner, search path, and expected outcome for zero, one, and many affected rows. Check version documentation for restrictions involving event combinations, columns, partitions, and table forms.
- Use descriptive OLD TABLE and NEW TABLE aliases.
- Never depend on an implicit row order.
- Keep the trigger in the originating transaction.
- Describe failure and rollback behavior.
Prefer set-based operations
Join, aggregate, or insert from the transition relation as a set. This usually produces clearer invariants and fewer queries than iterating rows in procedural code. Make duplicate and null handling explicit in keys and aggregates.
If an audit destination needs stable event identity or ordering, create those values deliberately. A transition relation does not guarantee business-event grouping across several SQL statements issued by one API request.
Bound recursion and privileges
Inventory triggers on both source and destination tables. Decide whether writes made by the function can fire the same workflow again, then prevent unintended recursion through architecture and explicit guards rather than session-global assumptions.
Set function ownership and SECURITY DEFINER usage conservatively. Qualify database objects, constrain search_path, grant only required privileges, and prevent untrusted callers from substituting objects resolved by the trigger.
Plan for large statements
Benchmark bulk writes at production-like row widths and cardinality. Observe temporary storage, memory, WAL, lock duration, downstream index work, replication lag, and latency. A set-based trigger can still make a formerly cheap bulk statement operationally expensive.
Keep external network calls and non-transactional side effects out of trigger execution. For durable asynchronous work, write an outbox row in the same transaction and let an idempotent worker process it after commit.
Test the deployed topology
Use Flashman's SQL formatter for DDL and queries, JSON formatter for synthetic rows, diff for expected row sets, timestamp converter for lock timelines, and cron helper for controlled maintenance windows.
Cover empty and bulk changes, all configured events, conflict paths, partitions, cascades, other trigger ordering, recursive writes, permission boundaries, statement errors, rollback, deadlocks, replicas, restore, migration rollback, and mixed application versions.