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
