Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
> 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
Sorry for top posting - but this is an annoying of this web interface to email.
:-(
Isn't what you're doing here a misuse of the idea of a transaction. I don't
claim to be an expert in this, but I thought the idea of a transaction was that
you bundle a group of statements together that must all succeed or all fail
together; so if any one of them fails, all the preceding work completed in the
transaction gets rolled back. Did I misunderstand something here, then? If
not, then the operations you describe for your first transaction really ought
not be in the same transaction.
Now, when I run a script of SQL statements in MS SQL Server, a statement that
encounters an error will simply stop, and then control can be passed to the
next SQL statement in the script, BUT THIS IS NOT IN A TRANSACTION!!! It is
only a script, and in the context in which I was working, I didn't need to use
transactions. And, I can provide additional options in the statements that
will modify this behaviour, so the script aborts at the error condition rather
than continuing with the next SQL statement in the script. With some
statements, what I would regard as an error requiring the script to abort seem
to be regarded as merely a warning by default. For example, in a bulk load of
data into a table, and there is a problem with the data for the tenth field on
the second row, the first row, which is correct, is stored, the statement
aborts at the problem row, and control is passed to the next SQL statement. In
my situations, I had other programs that would clean up the
data if this sort of problem arises, so where the problem is seen by default
as warranting only a warning, I could upgrade it to be regarded as an error.
And of course, there are options for controlling how the script behaves when an
error occurs. Perhaps that is involved in the behaviour you are reporting for
MS SQL Server?? I haven't investigated this myself as I haven't had the
problem you describe.
I didn't quite understand your description, in another post, of how Spring
treats your database statements. Am I to understand it puts all your SQL
statements into a single transaction? If so, either they badly mishandle
transactions or they are working with a very different concept of what a
transaction is.
One last question. You describe part of your problem as being correct
addition of data to an audit table. If I haven't misunderstood what you're
doing, isn't it incomplete if you record only statement failures? When I deal
with audits, I put the logic into triggers whenever possible. And I'd maintain
an independant error log from my application code, probably in a catch clause,
and this either goes to a flat file or uses an independant connection to the
database. And my practice is to use separate try/catch blocks for each
statement that can throw an exception, to improve the granularity of error
handling logic. That is the only way to have a chance of getting one statement
to execute regardless of whether or not a preceding statement throws an
exception.
I have a special interest in this because I am just beginning to look at
Spring (I downloaded it just a few days ago).
Cheers,
Ted
Tyson Lloyd Thwaites <[EMAIL PROTECTED]> wrote:
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 wi
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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
[EMAIL PROTECTED]
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 //
---(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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
> 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 ---(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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
Gregory Stark wrote:
"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> writes:
Gregory Stark wrote:
"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> 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 {
} catch (Exception e) {
}
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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>
>>"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> 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 {
>
> } catch (Exception e) {
>
> }
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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:
- 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 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 {
} catch (DataIntegrityViolationException e) {
}
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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
> 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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
Gregory Stark wrote:
"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> 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 {
} catch (Exception e) {
}
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
---(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
Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework
"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] [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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
