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