Re: Nested Transactions, Abort All

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:

> If we change the syntax, say by using SUBCOMMIT/SUBABORT for
> subtransactions, then using a simple ABORT would abort the whole
> transaction tree.

This seems like a non-starter to me. That would make it impossible to write
SQL generic code that could be used from within a transaction or as a
top-level transaction.

Consider for example if I have application code that normally handles
archiving old data (excuse the odd made-up pseudo-code syntax):

archive_table($tab, $date) {
query("
BEGIN
INSERT INTO archive_$tab (select * from $tab where date < ?)
DELETE FROM $tab where date < ?
END
", $date, $date);
}

Then I later decide I sometimes want to do that along with other jobs together
in a transaction. I can't just do:

query("BEGIN");
archive_table(tab1, date);
archive_table(tab2, date);
other_maintenance_work();
query("END");

Because then the archive_table() function would get an error from trying to use
BEGIN when it would need a SUBBEGIN. And it would not be any better if I
change archive_tab to use SUBBEGIN because I might be using it directly
elsewhere.

This seems like a irregularity in the API that makes sense only from an
implementation point of view. Top level transactions may be very different
from the implementation side, but from the user side they should really be
presented as being exactly the same as successive levels.

I do think a COMMIT ALL and ABORT ALL would be useful, but not for end-users.
I think they would be useful at a lower level. For example a web site could
issue an ABORT ALL at the end of generating the page to ensure any uncommitted
transactions fail and the connection is restored to a usable state for the
next page request.

--
greg