> 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.
> ... 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.
Why not have your error handler issue a new BEGIN statement? Or an
END/BEGIN pair if that is what is needed? afaik that wouldn't be any
different than the proposed "CONTINUE TRANSACTION" statement.
Or do you really want to commit the previous statements in the
transaction rather than throw them away? If so, you didn't really need
the transaction block there anyway, since you are willing to accept a
partial result.
But I'm vaguely recalling that my Ingres apps used to be able to choose
between COMMIT and ROLLBACK when an error occurred in a transaction.
I'll bet that is the behavior you are asking for. Don't know whether the
Postgres backend knows what the state prior to the current command was.
> 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.
That sounds like a good idea to me too. Are there any downsides to it?
- Tom
