Savepoints

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