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

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.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?

The way to do it in Postgres is with the SAVEPOINT command.

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.

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

Gregory Stark wrote:

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.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....

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

This may or may not be a design flaw in postgresql, but perhaps you
could reduce the emotional pitch of your emails - some people here
(and I am not one of them, to be honest) put a lot of work for no
payoff except the pride of contributing to a good product, and to have
it insulted seems, well, personally insulting, not to mention
unprofessional and a little silly and not something that belongs on a
listserv.

That being said, I feel your pain. Perhaps we can take this as an
opportunity to make the product better, rather than an opportunity to
bag on it by calling it "quirky" and "a nightmare".

True... apologies for any offence caused. Tweaking emotion levels.....
done :)

The core problem is that we are maintaining a fairly mature app, and the
behaviour in question means we effectively can't integrate with
postgresql (which is a shame - I really *really* want to... we currently
ship with MSDE!). We can't really re-engineer our transaction
demarcation, we need to be able to handle errors and continue.

It seems like something that would be fairly easy to change... I don't
know. I read someone talking about putting automatic checkpoints on
every statement that goes through the jdbc driver to get around this
issue. Perhaps this is the only way... unless pg could be put in a mode
where it did this for you... like autocommit, but auto-checkpoint.

Otherwise, I love everything else about postgresql, such as partitioning
etc, which I would love to implement if I knew our app could recover
from errors.

Thanks,
TLT

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

This may or may not be a design flaw in postgresql, but perhaps you
could reduce the emotional pitch of your emails - some people here
(and I am not one of them, to be honest) put a lot of work for no
payoff except the pride of contributing to a good product, and to have
it insulted seems, well, personally insulting, not to mention
unprofessional and a little silly and not something that belongs on a
listserv.

That being said, I feel your pain. Perhaps we can take this as an
opportunity to make the product better, rather than an opportunity to
bag on it by calling it "quirky" and "a nightmare".

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

On Fri, Aug 17, 2007 at 03:18:30PM +0930, Tyson Lloyd Thwaites wrote:

It seems like something that would be fairly easy to change... I don't
know. I read someone talking about putting automatic checkpoints on
every statement that goes through the jdbc driver to get around this
issue. Perhaps this is the only way... unless pg could be put in a mode
where it did this for you... like autocommit, but auto-checkpoint.

FWIW, this is exactly how the psql client handles "ON_ERROR continue".
There is resistance to building it into the protocol itself since the
autocommit fiasco. The backward compatability issues are daunting and
as pointed it can be handled adequately inside the driver
(jdbc/odbc/etc).

(That's not to say it couldn't be made easier for the clients also. For
example as part of the query request have a flag that says: start
savepoint prior to execution.)

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

The auditing is an interesting question, to which I do not have an
answer. Seems impossible to do in a transaction, by definition (ie
not product specific). Thoughts?

We do this with MSSQL. I have never given it a second thought until now.
If anything goes wrong we send an audit event, usually in a catch block,
all in the same transaction. If we have caught the exception, Spring
will commit, and the 'good' statements will stick.

The best example of this is if we can't delete a record, we will 'mark'
it instead, for example:

try {
<delete record>
} catch (DataIntegrityViolationException e) {
<mark record deleted/invisible>
}

These deleted records are then cleaned up at a later stage. This works
fine for MSSQL, I must admit we are not actively maintaining our Oracle
support.

Martijn van Oosterhout wrote:

On Fri, Aug 17, 2007 at 03:18:30PM +0930, Tyson Lloyd Thwaites wrote:

It seems like something that would be fairly easy to change... I don't
know. I read someone talking about putting automatic checkpoints on
every statement that goes through the jdbc driver to get around this
issue. Perhaps this is the only way... unless pg could be put in a mode
where it did this for you... like autocommit, but auto-checkpoint.

FWIW, this is exactly how the psql client handles "ON_ERROR continue".
There is resistance to building it into the protocol itself since the
autocommit fiasco. The backward compatability issues are daunting and
as pointed it can be handled adequately inside the driver
(jdbc/odbc/etc).

(That's not to say it couldn't be made easier for the clients also. For
example as part of the query request have a flag that says: start
savepoint prior to execution.)

Have a nice day,

I am not familiar with the autocommit fiasco, but I can use my
imagination... :)

You mention it can be built into the driver - is this a 'could be' or
'has been', ie is this a mainstream practice, or would such a thing be
considered risky? I would think it would be wonderful to allow users to
choose to override this behaviour - and suffer the consequences if
necessary! It's like playing with the fsync setting... turn it off at
your own risk.

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

Our app uses system state. We scan filesystems and record file
information in a database.

Here is one example:

<txn started by Spring in web container>
- insert 250 files
- update some stats (MUST work even if insert fails)
- update agent last-contact time (also must work so we know it's not awol)
<txn commited by Spring in web container>

When last insert has finished, server will kick into summary mode:

<txn started by Spring in web container>
- wait for asynchronous inserts to finish
- summarise data
- do some misc operations
- switch back to 'Idle' state
<txn committed by Spring in web container>

That last step is where the trick is: if anything goes wrong with the
previous operations, we MUST be able to put the system back into an
'Idle' state. Currently we do this in a catch block, ie:

try {
<do the summary stuff>
} catch {
<switch back to idle>
}

Now of course that won't work in pg. We use the same logic all through
the system, so you can see my problem. For example, if the user deletes
a drive record that still has referential links to it, we do this:

try {
<delete drive>
} catch (ReferentialIntegrityException e) {
<mark drive as deleted so it becomes invisible, is deleted later>
}

We rely on the fact that we can still do things and commit a transaction
even if a single statement has failed.

The above drive delete case is where I first noticed the problem when
switching from MSSQL: instead of the server handling the exception and
doing something else instead, the 'something else' also threw an
exception (txn is aborted), which propagated to the client end.

UPDATE: Webb has proposed that this behaviour may be MSSQL misbehaving.

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

On Fri, Aug 17, 2007 at 04:10:24PM +0930, Tyson Lloyd Thwaites wrote:

I am not familiar with the autocommit fiasco, but I can use my
imagination... :)

The changed transaction semantics caused much havoc with librarys and
drivers because client program could change the setting and driver no
long had any idea when their queries would commit.

You mention it can be built into the driver - is this a 'could be' or
'has been', ie is this a mainstream practice, or would such a thing be
considered risky? I would think it would be wonderful to allow users to
choose to override this behaviour - and suffer the consequences if
necessary! It's like playing with the fsync setting... turn it off at
your own risk.

Looking at the archives it's definitly been discussed but looks like
it's waiting for someone to implement it... (Odd, I wouldn't have
thought it was so difficult).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

It looks like it would be best if we re-worked our transactions and
controlled them manually for the portions that need it. It looks like we
have inadvertently been relying on a nasty 'quirk' ;) in MSSQL. I would
rather not go down the path of doing workarounds to make pgsql work like
mssql.

It is still a possible point of confusion, but I am starting to think
that pgsql has it right, and mssql has it wrong. (I feel betrayed!) This
issue probably deserves a prominant place in the FAQ!

Thanks for your help everyone.

Our app uses system state. We scan filesystems and record file
information in a database.

Here is one example:

<txn started by Spring in web container>
- insert 250 files
- update some stats (MUST work even if insert fails)
- update agent last-contact time (also must work so we know it's not
awol)
<txn commited by Spring in web container>

When last insert has finished, server will kick into summary mode:

<txn started by Spring in web container>
- wait for asynchronous inserts to finish
- summarise data
- do some misc operations
- switch back to 'Idle' state
<txn committed by Spring in web container>

That last step is where the trick is: if anything goes wrong with the
previous operations, we MUST be able to put the system back into an
'Idle' state. Currently we do this in a catch block, ie:

try {
<do the summary stuff>
} catch {
<switch back to idle>
}

Now of course that won't work in pg. We use the same logic all through
the system, so you can see my problem. For example, if the user
deletes a drive record that still has referential links to it, we do
this:

try {
<delete drive>
} catch (ReferentialIntegrityException e) {
<mark drive as deleted so it becomes invisible, is deleted later>
}

We rely on the fact that we can still do things and commit a
transaction even if a single statement has failed.

The above drive delete case is where I first noticed the problem when
switching from MSSQL: instead of the server handling the exception and
doing something else instead, the 'something else' also threw an
exception (txn is aborted), which propagated to the client end.

UPDATE: Webb has proposed that this behaviour may be MSSQL misbehaving.

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

It is still a possible point of confusion, but I am starting to think
that pgsql has it right, and mssql has it wrong. (I feel betrayed!) This
issue probably deserves a prominant place in the FAQ!

Betrayed? yes. Surprised? I hope not :)

I think your driver (JDBC? or?) is doing autocommit (though I am not
sure), and in the discussion we are confounding confusing rollbacks
(in the database) with exception handling (in the application,which, I
presume, is Java?).

You may be best reworking your transactions, but in order to clear up
the confusion you will probably need to post some actual code here (we
can handle it, don't worry...).

-W

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.au> writes:

Gregory Stark wrote:

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.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

Gregory Stark wrote:

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.au> writes:

Gregory Stark wrote:

"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.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.

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

You are right, it is a Java webapp.

I could post code, but the actual statements I am running are just plain
sql (wrapped in wrappers of wrapped wrappers...) which are run in a DAO
object in the third layer of the app. I would have to post reams of
code, which would break my non-disclosure agreement unfortunately. The
real issue is the fact that Spring intercepts certain requests and wraps
them in a transaction (a big one around many smaller statements), but I
want the luxury of allowing some of those statements to fail, but keep
going with the 'grand picture' transaction, if you catch my drift. To
post any useful code I would probably have to code up a simple JDBC test
case that demonstrates the problem.

Here is a real world example, though: after we have filled up a certain
table with records, we then create all the indexes on it. The problem is
that because of the design, there is no guarantee that this has not
already happened. So in this case, I would like to be able to say "try
creating indexes on the table, but you get an error because they are
already there, then continue on". I think this can only be done using
checkpoints.

I am not opposed to introducing checkpoints to our API, but it would be
nicer if I didn't have to. At the moment I have resigned myself to
turning off spring declarative txns for certain methods, and handling
them manually by doing multiple txn blocks. In the above example
however, the bit that I want to allow to fail is inside a method that
would have to be wrapped in a transaction.... .... see the web of
complexity that is growing?

It is still a possible point of confusion, but I am starting to think
that pgsql has it right, and mssql has it wrong. (I feel betrayed!) This
issue probably deserves a prominant place in the FAQ!

Betrayed? yes. Surprised? I hope not :)

I think your driver (JDBC? or?) is doing autocommit (though I am not
sure), and in the discussion we are confounding confusing rollbacks
(in the database) with exception handling (in the application,which, I
presume, is Java?).

You may be best reworking your transactions, but in order to clear up
the confusion you will probably need to post some actual code here (we
can handle it, don't worry...).

-W

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

On Fri, 17 Aug 2007, Tyson Lloyd Thwaites wrote:

It looks like it would be best if we re-worked our transactions and
controlled them manually for the portions that need it.

I am glad you have moved so quickly through grief and into acceptance.

It is still a possible point of confusion, but I am starting to think that
pgsql has it right, and mssql has it wrong. (I feel betrayed!) This issue
probably deserves a prominant place in the FAQ!

In whose FAQ? Would need more details about what MS-SQL is doing badly to
cover it on this side of things. There's been some collecting of trivia
in that category lately, haven't gotten to issues with their product much
so far.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:

On Fri, 17 Aug 2007, Tyson Lloyd Thwaites wrote:

It looks like it would be best if we re-worked our transactions and
controlled them manually for the portions that need it.

I am glad you have moved so quickly through grief and into acceptance.

Heh heh - maybe I've had good councellors...

It is still a possible point of confusion, but I am starting to think
that pgsql has it right, and mssql has it wrong. (I feel betrayed!)
This issue probably deserves a prominant place in the FAQ!

In whose FAQ? Would need more details about what MS-SQL is doing
badly to cover it on this side of things. There's been some
collecting of trivia in that category lately, haven't gotten to issues
with their product much so far.

Pgs... like a warning that you can't do this;

begin
insert 1 --works
insert 2 --fails
commit

row 1 will exist in db (yes, no kidding). This will not work in pg,
which I now see is obviously correct.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Pgs... like a warning that you can't do this;

begin
insert 1 --works
insert 2 --fails
commit

row 1 will exist in db (yes, no kidding). This will not work in pg,
which I now see is obviously correct.

This should either a FAQ for MS-SQL or Spring, but since PG does it
canonically it doesn't actually get asked very frequently ...

Sounds like a nightmare specifically designed for vendor lock-in. My
condolences.

I am not sure how you can insert into a log even with savepoints,
unless you put the logging statement first and then follow it with the
insert.

Tyson Lloyd Thwaites wrote:

I am not opposed to introducing checkpoints to our API, but it would be
nicer if I didn't have to. At the moment I have resigned myself to
turning off spring declarative txns for certain methods, and handling
them manually by doing multiple txn blocks. In the above example
however, the bit that I want to allow to fail is inside a method that
would have to be wrapped in a transaction.... .... see the web of
complexity that is growing?

Isn't the 'try' statement rather similar to a 'savepoint' command? I
realize it would be difficult to override the behaviour of try {...}
catch (...) {...}, but it shouldn't be too hard to wrap it somehow for
exceptions in database code.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Webb Sprague wrote:

I am not sure how you can insert into a log even with savepoints,
unless you put the logging statement first and then follow it with the
insert.

and delete it after success?

Alternatively you could use one connection for your normal queries, and
another for auditing. Your application will need twice as many
connections that way, though... In that case you shouldn't commit
records on the 'normal' connection before the audit records have been
committed I suppose?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //