Implement SAVEPOINT (and friends.)

[jonathanstowe]

There are still some outstanding infelicities with transactions (such as e.g #507 ) particularly with the way that inner hidden transactions are dealt with by creating a new connection that still may lead to unexpected behaviour (largely because changes in the outer transaction will be isolated from those in the inner one.)

Implementing 'SAVEPOINT' (and ROLLBACK TO SAVEPOINT, COMMIT SAVEPOINT and RELEASE SAVEPOINT,) would go some way to addressing this: it would no longer be necessary to create a new connection to deal with the inner transactions, rather it would be possible to implicitly translate any nested BEGIN -> COMMIT/ROLLBACK to use the savepoint equivalents instead.

It may even simplify a solution to the #507: the driver will maintain a list of the savepoints each with their own Promise, the rollback, commit or release of the savepoint will keep the promise and the outer commit or rollback will wait on the promises before issuing the commit or rollback to the engine.

Savepoints are widely implemented, there are some small difference but they are trivially dealt in the drivers:

https://www.postgresql.org/docs/current/sql-savepoint.html
https://dev.mysql.com/doc/refman/8.0/en/savepoint.html
https://www.sqlite.org/lang_savepoint.html

[jonathanstowe]

Example of how DBIx::Class implements this https://metacpan.org/dist/DBIx-Class/view/lib/DBIx/Class/Manual/Cookbook.pod#Nested-transactions-and-auto-savepoints transparently.

[jonathanstowe]

FWIW another motivation for this is that I have a possible need for an extension of Test::Class whereby each test method is wrapped in a transaction that will always rollback when done, as it currently stands this can't work with nested transactions being in a separate connection.

[FCO]

That sounds great!

I'm not sure when I'll be able to work on that, but I totally agree that's a great solution!

Thanks