[HACKERS] Error handling in transactions - Mailing list pgsql-hackers

First, discoverability of the current ON_ERROR_ROLLBACK=interactive is poor. How would a user ever know that this was available as an option they may want to set? Even if they could be told it was an option (in say a hint message on a transaction abort) they would only find out about this after the fact when the damage (a lost transaction) was done.

So let's try and imagine a solution where a user who has made a mistake in a transaction might be able to gracefully recover but where the current semantics are preserved.

In this case, we'd want a transaction not to abort immediately (allowing recoverability) but not to commit if there was an error.

To make this work, an error during a transaction would not trigger an immediate ROLLBACK but would instead set a session state say, ERROR_TRIGGERED.

Most statements would not be allowed to execute in this state and each statement would return an error describing the current state. A COMMIT would then finally trigger the ROLLBACK, closing the transaction scope. 

If the user were interested in recovering their transaction, they could set ERROR_TRIGGERED back to "false", send any commands they wanted (retrying part of the transaction, or whatever.) It might be simplest to prevent all statements besides reading or setting ERROR_TRIGGERED but it may be desirable to allow non-DDL/DML statements in order to aid in diagnosing what happened.

This would also allow for programmatic error handling during transactions without the overhead of savepoints by checking the value of ERROR_TRIGGERED after each statement and handling it as appropriate.

Of course, the additional complexity of this feature is greater than simply updating a default value but I'm certainly willing to accept the argument that setting a new default to a potentially destructive setting is problematic. Still, I do believe that the current state of affairs is painful and problematic and this is a problem worth solving.

--