Tim et al.,
Anyone given any thought to an interface for savepoints? They're a
part of the SQL standard, and basically look like named
subtransactions. The SQL looks like this:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
RELEASE SAVEPOINT my_savepoint;
COMMIT;
Compared to transactions, the statements look like this:
TRANSACTIONS | SAVEPOINTS
-------------------------------------
BEGIN; | SAVEPOINT :name;
COMMIT; | RELEASE :name;
ROLLBACK; | ROLLBACK TO :name;
Given these terms, I think that DBD::Pg takes the correct approach,
offering these functions:
pg_savepoint($name)
pg_release($name)
pg_rollback_to($name)
All you have to do is pass a name to them. I'd therefore propose that
the DBI adopt this API, offering these functions:
savepoint($name)
release($name)
rollback_to($name)
The would essentially work just like transactions in terms of error
handling and whatnot. The example might look like this:
$dbh−>{RaiseError} = 1;
$dbh->begin_work;
eval {
foo(...) # do lots of work here
$dbh->savepoint('point1');
eval {
bar(...) # including inserts
baz(...) # and updates
};
if ($@) {
warn "bar() and baz() failed because $@";
}
$dbh−>commit; # commit the changes if we get this far
};
if ($@) {
warn "Transaction aborted because $@";
# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh−>rollback };
# add other application on−error−clean−up code here
}
If the transaction succeeds but the savepoint fails, the foo() code
will be committed, but not bar() and baz().
Thoughts?
Best,
David
