I've never really been very happy with the decision early on in the
development of nested transactions to use savepoints in the way they
were implemented in the command structure. Savepoints are nearly
useless for sql scripting because there is no way to probe a
transaction and handle error conditions appropriately without dipping
into a function -- which puts severe limits how savepoints might be
utilized. I suspect the savepoint command is almost never used
outside of oracle compatibility efforts. [I'm not taking away from NT
here, begin...exception..end is incredibly useful and I'm sure widely
used]
The missing piece of the puzzle is the ability to recover a failed
transaction without issuing a full commit/rollback. This could be a
new flavor of the savepoint command, commit command, or a new command.As a bonus, upon recovering the transaction you
couldsnap an sql
statement...this would be great for scripting:
BEGIN;
SAVEPOINT X;
COMMIT ON ERRORS SELECT FOO();
--or--
BEGIN;
SAVEPOINT x;
SAVEPOINT y ON ERRORS SELECT FOO; -- (or ROLLBACK TO SAVEPOINT x);
COMMIT;
comments? fast track to todo list? :-)
merlin