2026-09-24 · 6 min read · Rahul Chitturi
- sql
- postgresql
- triggers
PostgreSQL transition relations let an AFTER statement-level trigger inspect all rows affected by one statement through OLD TABLE or NEW TABLE names. They can make auditing and set-based maintenance efficient, but their contents follow the triggering statement rather than an application request.
Surprises appear when an UPDATE affects zero rows, an upsert takes different paths, another trigger changes values, or code expects one trigger call per row.
Inspect the exact trigger definition
Record the server version, table kind, event, timing, level, REFERENCING clause, trigger order, WHEN conditions, function definition, security context, and every other trigger on the table. Confirm that the requested transition relation is legal for that trigger form.
- Separate OLD TABLE from NEW TABLE semantics.
- Do not assume transition rows have a stable order.
- Handle empty affected sets deliberately.
- Qualify objects and control the function search path.
Trace one statement as a set
Reduce the failure to a transaction with synthetic rows and capture the base statement, row count, transition-relation contents, downstream writes, and final state. Use set-based joins and aggregates instead of loops when the business rule permits.
Guard against recursive writes and define whether cascades or trigger-generated statements produce separate trigger activity. Concurrency rules still depend on transaction isolation and locks; the transition relation is not a global change feed.
Test operational boundaries
Use Flashman's SQL formatter for trigger DDL and diagnostic queries, JSON formatter for public row fixtures, diff for expected audit sets, and timestamp converter for transaction timelines.
Test zero, one, and many rows; INSERT, UPDATE, DELETE, and supported conflict paths; unchanged values; partitions; other triggers; rollback; deadlocks; large statements; replicas; permissions; migration retries; and application-version overlap.