Re: [RESEND] Transaction auto-abort causes grief with Spring Framework

Gregory Stark wrote:

>"Tyson Lloyd Thwaites" <tyson(dot)lloydthwaites(at)allianza(dot)com(dot)au> writes:
>
>
>
>>Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us
>>keep going.
>>
>>
>
>How do you catch exceptions in these other dbs?
>
>
plain java try/catch. In other dbs, if I am in a txn, and I run 3
statements, and one of them fails, spring's jdbc wrapper will throw an
exception up to the wrapping template, which will roll the txn back.
However if I wrap one of those statements in a
try/catch(RuntimeException), then the exception will not propagate, but
the db will not automatically fry the whole transaction either.

>The way to do it in Postgres is with the SAVEPOINT command.
>
>
Yeah, but that is totally unrealistic in our case. I can't go sprinkling
"if current db is pgsql then savepoint here" code all through the app.
It's a bit annoying that pg insists on being so different to others in
this respect, especially since the spec does not mandate it, and appears
even to say that the transaction should be allowed to continue. (I read
this in another pg thread, I will have to find the argument there, it
was a good one). I wish it could be turned off - it seems a bit
draconian and heavy-handed to me.

>
>
>>For example, if something goes wrong, I can't even write an event row to our
>>auditing table!
>>
>>
>
>This is actually a somewhat more complex example than handling an expected
>error. For audit records you really want to be able to commit the audit record
>independently of the rest of the transaction. These are called "autonomous
>transactions" and there's no built-in support for them in Postgres but you can
>put together something equivalent using dblink or a second client connection.
>
>
it is more like this:

try {
<do something>
} catch (Exception e) {
<insert into audit table: hey, it broke>
}

We use notifications that get sent via email and recorded in an event
table. So at various places we might notify the user that we had to
cancel a process because of an error, and then record the fact that we
sent this email... it is all so easy with other dbs, but with postgresql
it is a nightmare... the only solution I can see is to remove the
declarative transactions in Spring and start using manual transactions
blocks around everything that could possibly go wrong... just because of
a quirk in postgresql....