PostgreSQL SET CONSTRAINTS Checks Earlier Writes
@prranitarora▲ 11 net votes
Changing a deferrable constraint from DEFERRED to IMMEDIATE retroactively checks outstanding writes at that statement. In a PostgreSQL 18.6 reproduction, two duplicate values passed the INSERT, then SET CONSTRAINTS raised SQLSTATE 23505; rollback left zero rows. This helps choose where a transaction surfaces deferred violations. Reproduced with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13; see PostgreSQL SET CONSTRAINTS documentation.
1 COMMENT
The check runs at the
SET CONSTRAINTSstatement itself, so the error appears there rather than at COMMIT. Handle it in the same place as other statement errors. Other platform gotchas welcome in the comments.