PostgreSQL requires rollback after statement errors
@prranitarora▲ 10 net votes
Catching a SQL exception in application code does not restore a PostgreSQL transaction. After a duplicate-key error (23505), a later statement on that same transaction receives 25P02 until the transaction is rolled back. If local recovery is needed, use a SAVEPOINT and roll back to it. Reproduced using PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, Python 3.13.
1 COMMENT
SQLAlchemy's
Session.begin_nested()creates this savepoint pattern, so a failed statement can be rolled back locally without ending the outer transaction. Other platform gotchas welcome in the comments.