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

"Tyson Lloyd Thwaites" <tyson(dot)lloydthwaites(at)allianza(dot)com(dot)au> writes:

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

Well you could suggest to your driver that it should wrap your queries in a
savepoint and throw a java exception in case it gets an error.

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

Well that doesn't work if you have more statements to execute within the same
transaction. Yo would still want the audit record to be committed even if you
subsequently roll back the transaction.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com