2026-09-21 · 8 min read
- sql
- postgresql
- transactions
Deferrable constraints let a transaction pass through temporary states that violate selected uniqueness, primary-key, foreign-key, or exclusion rules before reaching a valid final state. They are useful for operations such as reordering unique positions or inserting mutually related rows.
Deferral changes when a rule is checked, not whether it applies. Commit can fail after every earlier statement succeeded, so the application must treat transaction completion as a real failure boundary.
Use deferral only for a real transaction need
Start from an operation that cannot reasonably preserve the invariant after every statement but can preserve it at the transaction boundary. Prefer immediate checks for ordinary writes because they localize errors and reduce the amount of invalid intermediate state.
- Confirm the constraint type supports deferral.
- Choose INITIALLY IMMEDIATE or DEFERRED deliberately.
- Name constraints for targeted mode changes.
- Keep transactions short and bounded.
Control timing on the correct connection
SET CONSTRAINTS operates inside a transaction and affects eligible constraints for that transaction. With connection pools and ORMs, ensure the mode change and all dependent writes use the same connection and are not split by implicit commits.
Changing a constraint to immediate can trigger validation at that statement. Document whether checks occur after each statement, when explicitly forced, or at commit, and align application error handling with each boundary.
Handle commit as part of the operation
Do not report success or trigger irreversible external side effects before commit succeeds. A deferred violation, deadlock, serialization failure, network interruption, or server shutdown can still abort the transaction at its final step.
Use a transactional outbox when external work must follow a committed change. Retry the complete transaction only under a bounded policy with fresh reads and an idempotent operation identity.
Test schema and concurrency behavior
Use Flashman's SQL formatter for DDL and transaction scripts, diff for catalog snapshots, JSON formatter for synthetic records, timestamp converter for event order, and UUID generator for retry identities.
Test immediate and deferred modes, forced checks, savepoints, multiple violations, circular references, reordered unique values, concurrent writers, deadlocks, serialization failures, pooled connections, ORM flushes, commit errors, migrations, and rollback.