Re: Savepoints

On Oct 5, 2009, at 5:01 AM, Tim Bunce wrote:

>> We already have the example of DBI transaction support; savepoints  
>> are just
>> an extension of that.
>
> Well, as you said earlier David, "Transactions <> Savepoints."

No, but they're closely related.

> Commit and rollback are methods partly to support drivers that emulate
> transactions n some way, partly to ensure client and server state stay
> in sync, and partly to allow, at least in theory, the use of  
> transaction
> managers.

The same would apply to savepoints, which are perhaps best thought of  
as subtransaction markers.

> I'd be interested if someone could do the research to list what
> databases support savepoints and what syntax they use for the main
> statements.

DBIx::Class has done this for a lot of databases. Check out

     http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/lib/DBIx/Class/Storage/DBI/

A grep in there finds support in MySQL, PostgreSQL, MSSQL, and Oracle:

MSSQL:
     SAVE TRANSACTION $name;
     ROLLBACK TRANSACTION $name;

MySQL:
     SAVEPOINT $name;
     RELEASE SAVEPOINT $name;
     ROLLBACK TO SAVEPOINT $name;

Oracle:
     SAVEPOINT $name;
     ROLLBACK TO SAVEPOINT $name;

Pg:
     $dbh->pg_savepoint($name);
     $dbh->pg_release($name);
     $dbh->pg_rollback_to($name);

DBD::Pg is the only one to offer an API for it in the database handle.  
It looks like it uses a protocol-level command to do the work, but the  
database itself supports the SQL-standard interface that's identical  
to what mysql supports. Oracle's implementation is standard, except  
for the lack of a release command, which is apparently implicit.  
MSSQL's interface also lacks a release, and the names for the commands  
are non-compliant.

To me, this stuff is so closely related to transaction management that  
there ought to be a uniform interface for it in the DBI. If DBI2  
doesn't have an API for transactions, then I wouldn't argue to add  
savepoints there. But it sure makes sense to me for the DBI.

Best,

David