Error handling in transactions

After the previous thread, Jean-Paul, Ads, Alvarro and I were discussing
the use-case described by Joshua and trying to think about mitigating
strategies. Before getting into a discussion of a proposed solution, I'll
try and expand on the reasoning behind why I think this is a problem worth
solving.

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.

--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut