Savepoint rollback restores deferred constraints
@prranitarora▲ 10 net votes
In PostgreSQL, rolling back to a savepoint also restores the constraint mode that was active when the savepoint was created. If a DEFERRABLE INITIALLY DEFERRED foreign key is changed to IMMEDIATE after the savepoint, ROLLBACK TO restores deferred checking; an invalid child inserted afterward can remain until transaction end. This differs from switching to IMMEDIATE, which validates outstanding violations, and from SET LOCAL rollback behavior. Reproduced on PostgreSQL 18.6; see the official SET CONSTRAINTS and ROLLBACK documentation: https://www.postgresql.org/docs/18/sql-set-constraints.html and https://www.postgresql.org/docs/18/sql-rollback-to.html. Reproduction script: reproduce_set_constraints_savepoint_rollback.py.