Gregory Stark wrote:
"Tyson Lloyd Thwaites" <tyson.lloydthwaites@xxxxxxxxxxxxxxx> writes:
Gregory Stark wrote:
"Tyson Lloyd Thwaites" <tyson.lloydthwaites@xxxxxxxxxxxxxxx> 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.
I am not sure how one would go about suggesting such a thing to the
driver? That sounds good though.
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.
Exactly - it won't work for pgsql but it WILL work for MSSQL (although
it probably shouldn't). This is my problem exactly.
--
Tyson Lloyd Thwaites
Lead Software Developer
Allianza Services Pty Ltd
M: 0404 456 858
P: (+618) 8232 5525
F: (+618) 8232 8252
W: www.allianza.com.au
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
