I've been using transactions fairly heavily as a means of ensuring that
a group of related updates would look atomic to other processes watching
the database. For some reason I'd never thought twice about the
implications of a transaction block for error recovery. But they're
there, and they're nasty: if one command in a transaction fails (and
this is a failure in the server's terms, not according to what the
application's logic is) then every subsequent command gets ignored
until an END TRANSACTION is seen.
I made the mistake of writing some code that did an INSERT it knew
might fail (due to key collision in a unique index). The code was
prepared to deal with the failure. No problem. Except it was being
executed in an application that likes to wrap a transaction block
around rather large chunks of processing. Result: hundreds of
subsequent commands were ignored, silently. OK, there were notices
getting printed on stderr, but that didn't help the application any.
Didn't help the user either in this particular case, because he had
iconized the shell window he'd started the application from. He
lost several hours' work, and I'm looking at a total redesign of the
application logic to get around this.
The reason I say "total redesign" is that I'm afraid to just rewrite
the chunk of code that does the one INSERT. Now that I've been bit
once, I know that transactions are horribly dangerous: get an error
that you *think* you've recovered from, and you're still screwed if
you forgot to issue an END or ABORT TRANSACTION. Your code won't
recover until you shut down and restart your app. So I'm going to
have to find a way to guarantee that all possible paths of execution
will do the END TRANSACTION, and that looks painful.
Is there, or could there be, such a thing as a "CONTINUE TRANSACTION"
command to clear the "aborted transaction" status after an expected
error? Without that, either I can't use transactions, or I can never
dare use a command that might return a failure message.
Also, I feel that it's a mistake to treat "queries ignored until END"
as a mere informational message. It ought to be transmitted to the
frontend as an *error condition*. Your command did not execute, and
pretending that there's not something wrong is not helpful, especially
not to application code that's not designed to pay close attention to
NOTICE messages.
regards, tom lane
