Re: Nested Transaction TODO list

Tom Lane wrote:

> Still need to agree about externally visible behavior (a different stmt
> than begin/commit for subxacts? What about savepoints?) Also, what about
> exposing this functionality in plpgsql? Seems like we need some kind of
> exception handling syntax to make this useful. What does Oracle do?

As I just mentioned in another thread, whatever the syntax for nested
transactions I'd like to see plain COMMIT/ABORT/ROLLBACK always affect
the top-level transaction.

Oracle appears to have:

SAVEPOINT savepointname
ROLLBACK [WORK] [TO [SAVEPOINT] savepointname]

You can issue SAVEPOINT with the same name while the old savepoint is
valid, and the name will be moved. Rolling back to a savepoint does not
invalidate that savepoint, i.e. you can roll back to a savepoint
multiple times.

One generalization of this to nested transactions would be:

SUBBEGIN [transactionname]
SUBCOMMIT [transactionname]
SUBABORT [transactionname]

SUBBEGIN outside an explicit transaction block works like BEGIN.

Active transactions may have names. SUBBEGIN with a name associates the
name with the new transaction; if the name is already in use, it's also
removed from the old transaction. Alternatively we could only look at
the most-deeply-nested transaction with a given name when specifying
transactions by name. That would make savepoint behaviour slightly
different to Oracle (Oracle could see a savepoint as invalid that we
consider valid), but it looks like it'd make things a bit easier for
procedural languages as functions can't accidentally trash a name
"belonging" to your caller so long as they resolve all transactions they
start.

SUBCOMMIT or SUBABORT work on the current transaction level (if no name
is specified) or all transactions down to (and including) the named
transaction level if a name is given.

"SAVEPOINT savepointname" becomes an alias for "SUBBEGIN savepointname".
"ROLLBACK TO [SAVEPOINT] savepointname" becomes an alias for "SUBABORT
savepointname; SUBBEGIN savepointname".

We could spell SUBBEGIN and friends differently -- is it better to add
more syntax to the existing transaction manipulation commands along the
lines of "BEGIN [NESTED] [TRANSACTION|WORK] [transactionname]",
"ROLLBACK [NESTED] [TRANSACTION|WORK] [transactionname]" etc?

Any comments?

-O