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
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,
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
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