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

Hi,

I know this issue has been discussed at length before, but postgresql's
behaviour of forcing a rollback when any error occurs is making life
very difficult for me. We use Spring's transaction proxies, which are
applied to methods in web controllers. In the backend code, if a runtime
exception occurs and we let it propagate, then Spring will catch it and
roll back the transaction for us. However, sometimes we need to catch
exceptions on certain ops that are allowed to fail, and let the rest of
the transaction proceed.

For example, in our app there may be many operations that are performed
in the course of a single web request: changing system state, updating
last request times, writing logs records, etc. Normally if we catch the
exception, other dbs (Oracle, MSSQL) will let us keep going. However
with postgresql, if something goes wrong that we would normally ALLOW,
it bombs the whole request! This is no good! :(

What has effectively happened is that postgresql has taken away my right
to allow certain non-fatal SQL errors to occur during a transactional
request. For example, if something goes wrong, I can't even write an
event row to our auditing table! Argh!! We do not have the option to
turn off the transactions at the top level; this would require a major
rework, and require us to put manual transaction blocks everywhere -
exactly what Spring helps us avoid.

Is there some way to turn this behaviour off without having to resort to
pg-specific code (ie sprinkling checkpoints everywhere)? We allow
customers to plug their chosen db into our app for a backend. This has
been fine until someone asked for postgresql... this behaviour is
different to the other big dbs. Is there any reason this behaviour is
not at least optional?

Thanks for listening. My research on other threads has not given me much
hope, but maybe things have changed recently?

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

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

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

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/
> From each according to his ability. To each according to his ability to litigate.

>> 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/
> From each according to his ability. To each according to his ability to litigate.

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

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

Here is one example:


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


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


- wait for asynchronous inserts to finish
- summarise data
- do some misc operations
- switch back to 'Idle' state


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 {

} catch {

}

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 {

} catch (ReferentialIntegrityException e) {

}

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


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

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

Yes, but I believe the OP was getting two levels of his application
mixed up:  he was doing something that caused a rollback in the
*database*, then hoping to recover in a catch block in the
*application* without terminating the aborted transaction in the
database.  Or so I gather.

You are right in another post about the purpose and design of
transactions, and don't use the discussion here as a model, though
drivers seem to often do weird stuff with transactions behind your
back.  Psycopg (python) does an implicit begin, so you must commit,
which then starts another begin automatically.  I think you can set  a
handle to do autocommit, but I never do. This seems best because it
forces you to handle transactions explicitly, but I can imagine other
(bad) approaches, and Spring may use them (though I think the
difficulty is that MS-SQL is sloppy, not Spring, and that the OP is
still getting used to TX's and MS-SQL covered up some things that
shouldn't have been covered).

W