Catching the exception does not undo the writes

Picture a bank entry that has been posted and balances. A reconciliation runs against it, fails, and logs the failure. Afterwards the entry is missing the two lines it used to balance against, and the replacement lines it should have gained were never written. Nothing crashed. The request returned 200.

The code looks defensive. The whole reconcile step sits inside a try, the except catches, logs and moves on, and the function returns a sensible result. Read on its own, that block says: if any of this goes wrong, nothing happens.

It does not say that. It says: if any of this goes wrong, stop running the rest of it. Those are different statements, and the gap between them is where the half-written entry came from. Whether the writes already issued survive is decided by something the except block never observes: whether the error came from your language or from the database.

The same transaction twice

A table standing in for the posted entry, with the two suspense lines a reconcile would clear:

create table line(
  id int primary key, entry int not null,
  kind text not null, amount numeric not null);
insert into line values (1,100,'suspense',250.00),(2,100,'suspense',-250.00);

The reconcile is two steps in one transaction. Step one deletes the suspense lines. Step two writes the counterpart lines that replace them. Between the two, something raises. The only thing that varies below is what raises.

Case A raises in application code, the kind of fault that has nothing to do with the database: an attribute read on a value that turned out to be None. Case B raises in the database, a not-null violation on the insert. Case C is case A again with the body wrapped in a savepoint. In all three the exception is caught and the transaction is then committed, exactly as the defensive block intends.

Run against native PostgreSQL 16.14 and 18.4 servers, which agreed on every line:

case	what raised inside the try	next statement after the catch	the COMMIT call	suspense lines left
A	AttributeError in application code	succeeds	returns without error	0
B	23502 not-null violation	25P02, see below	returns without error	2
C	AttributeError, body wrapped in SAVEPOINT	succeeds	returns without error	2

Case A is the entry from the opening. The delete committed. The suspense lines are gone, their replacements were never written, and the posted entry no longer balances. The handler caught the error, and the transaction committed the half of the work that had already been sent.

Case B is the part worth sitting with. The same handler, the same commit, and the data is intact. The database error did what the except block was written to do, and it did it by refusing to cooperate: after a failed statement, every subsequent command in that transaction returns

25P02  current transaction is aborted, commands ignored until end of transaction block

including a bare SELECT, which was checked separately because it is the claim that sounds least likely. The COMMIT then returns without raising, and discards everything: the server answers it with the command tag ROLLBACK, not an error. The application believes it committed. Nothing was written.

So the two cases fail in opposite directions. The error that came from the database protected the entry. The error that came from application code did not, and it is the one a reader of the except block would call harmless.

Why application errors are the dangerous ones

The asymmetry is not a quirk. It follows from where each error happens.

A database error happens on the server, which therefore knows about it and marks the transaction unusable. An AttributeError happens in the client process. Nothing is sent. The server's view of the transaction after the exception is exactly its view before it: open, valid, holding one uncommitted delete, waiting. That was confirmed directly rather than assumed. After the caught AttributeError, a SELECT ran, an INSERT ran, and neither returned 25P02. The transaction was not merely alive but ordinary.

Which gives the uncomfortable reading of except Exception. It is usually described as catching too much. The sharper problem is which half it catches too much of: it suppresses the database errors that would have aborted the transaction and saved the data, and it has no effect at all on the application errors that leave it committable. It is at its most dangerous against exactly the faults that have nothing to do with the database.

What the documentation says to do instead

PostgreSQL's own reference pages describe both halves. On the mechanism, the SAVEPOINT page:

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.

And ROLLBACK TO SAVEPOINT:

Roll back all commands that were executed after the savepoint was established and then start a new subtransaction at the same transaction level. The savepoint remains valid and can be rolled back to again later, if needed.

The abort behaviour from case B is documented too, in the psql reference, under the variable that exists to switch it off:

When set to off (the default), a statement in a transaction block that generates an error aborts the entire transaction. The error rollback mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.

That is the pattern, stated by the people who wrote the database, and it is what case C does by hand. A savepoint before the step, a rollback to it in the handler. Case C left both suspense lines in place, and the transaction stayed usable afterwards.

One detail in the order of operations is easy to get wrong, and the documentation is explicit about it. From RELEASE SAVEPOINT:

It is not possible to release a savepoint when the transaction is in an aborted state; to do that, use ROLLBACK TO SAVEPOINT.

Measured, in both versions: with the transaction aborted by the not-null violation, RELEASE SAVEPOINT returns 25P02 like everything else, and ROLLBACK TO SAVEPOINT succeeds and leaves the transaction able to run statements again. A handler that releases its savepoint on the way out, without rolling back first, works in testing and fails on the one path it was written for.

The construct that gets this right

Odoo's ORM carries this as a context manager, and its implementation is a compact statement of the rule. From odoo/sql_db.py, the Savepoint class:

def __exit__(self, exc_type, exc_val, exc_tb):
    self.close(rollback=exc_type is not None)

def close(self, *, rollback=True):
    if not self.closed:
        self._close(rollback)

def rollback(self):
    self._cr.execute(SQL('ROLLBACK TO SAVEPOINT %s', self._name))

def _close(self, rollback):
    if rollback:
        self.rollback()
    self._cr.execute(SQL('RELEASE SAVEPOINT %s', self._name))
    self.closed = True

Its docstring states the contract plainly: "The savepoint will be rolled back on unsuccessful context exits (exceptions). It will be released ("committed") on successful context exit."

Two things are worth noticing. __exit__ keys the rollback on exc_type is not None, any exception, not a database error: precisely the case A fault that the plain handler gets wrong. And _close rolls back before it releases, which is the only order that survives the aborted state, as measured above.

So the fix is not a better except clause. It is a savepoint around the step, which is the thing that makes a caught exception mean what the handler looks like it means. Each step of a multi-step write wants its own, and the handler has to name which step it is undoing. A single savepoint taken at the start of the request and rolled back to in a catch-all handler is a transaction rollback with extra syntax.

Where this does not hold

The protection in case B is a PostgreSQL behaviour, not a property of databases, and reading it as one is how the mechanism gets mis-learned. The same two cases against SQLite 3.45.1, through Python's sqlite3:

case	next statement after the catch	suspense lines left
A	succeeds	0
B	succeeds, transaction still usable	0

SQLite does not abort the transaction on a constraint violation. The retry after the failed insert succeeded, the commit committed, and the suspense lines are gone in both cases. The accidental rescue that made case B harmless on PostgreSQL does not exist here, and the half-written entry arrives whichever way the step failed.

The savepoint is the part that travels. Case C run against SQLite left both suspense lines in place, the same as PostgreSQL. The remedy is portable even though the hazard it protects against is not uniform, which is a reason to write it rather than to rely on whichever rescue the current engine happens to provide.

What was and was not checked

Every row in the tables above is output from a script run for this post. The PostgreSQL figures come from native PostgreSQL 16.14 and 18.4 servers, queried over TCP, and were repeated in PostgreSQL 16.4 and 18.3 compiled to WebAssembly (PGlite); all four agreed on every case. The SQLite figures come from SQLite 3.45.1 through Python's sqlite3. Each quotation above was taken from PostgreSQL's own documentation, read at version 16.15 as installed, and cross-checked against the reference sources on the REL_16_STABLE branch.

The Odoo excerpt is quoted from odoo/sql_db.py as fetched, and the claims made about it are claims about that file. This post does not say how any particular application handles its own reconciliation, because no application's code was read for it.

Nothing here is a claim about how often this happens. The topic came from an internal queue of recurring problems, and the individual reports behind it were not available while writing, so no count appears above and none should be read into it.

This post makes no claim about our own products.