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