Nested Transactions, Abort All

On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote:

Is there going to be an option to abort the complete transaction without
knowing how deep you are? Perhaps something like "ABORT ALL".

The reason I suggest this, is that I can foresee an application or user
leaving nested transactions open inadvertently, or not knowing how
deeply nested they are when they are called. It's just a thought, and I
didn't recall any mention of something like it on the list.

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

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Oh, oh, las chicas galacianas, lo har�n por las perlas,
�Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, �Prueba una hija de Caladan! (Gurney Halleck)

On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:

On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote:

Is there going to be an option to abort the complete transaction without
knowing how deep you are? Perhaps something like "ABORT ALL".

The reason I suggest this, is that I can foresee an application or user
leaving nested transactions open inadvertently, or not knowing how
deeply nested they are when they are called. It's just a thought, and I
didn't recall any mention of something like it on the list.

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

But then we're back to the application having to know if its in a
regular transaction or a sub-transaction aren't we? To me that sounds
just as bad.

"ABORT ALL" sure would be nice.

--
Mike Benoit <ipso@snappymail.ca>

On Thu, Jul 01, 2004 at 04:47:09PM -0700, Mike Benoit wrote:

On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:

On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote:

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

But then we're back to the application having to know if its in a
regular transaction or a sub-transaction aren't we? To me that sounds
just as bad.

I don't get it. You want to argue that the application should be
ignorant of whether it was in a transaction or not?

What I am saying is that independent of what the current nesting level
is, issuing ABORT would close all open subtransactions, close (roll
back) the main transaction too, and return to the default
not-in-a-transaction state.

Of course, issuing a single COMMIT would also commit all open
subtransactions and the main transaction too.

In contrast, issuing SUBCOMMIT or SUBABORT would commit/abort only the
current subtransaction.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los pari�, y tambi�n las mujeres,
aunque no vi m�s que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de m�s de XXX a�os" (Crist�bal Col�n)

Mike Benoit <ipso@snappymail.ca> writes:

On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:

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

But then we're back to the application having to know if its in a
regular transaction or a sub-transaction aren't we? To me that sounds
just as bad.

Someone (I forget who at this late hour) gave several cogent arguments
that that's *exactly* what we want. Please see the prior discussion...

Right at the moment I think we have a consensus that we should use
SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not
say we've agreed to exactly those keywords, only that it's a good idea
to make them different from the outer-level BEGIN/END keywords.)

There was also some talk of offering commands based around the notion of
savepoints, but I'm not sure that we have a consensus on that yet.

regards, tom lane

On Thu, 2004-07-01 at 22:14, Tom Lane wrote:

Mike Benoit <ipso@snappymail.ca> writes:

On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:

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

But then we're back to the application having to know if its in a
regular transaction or a sub-transaction aren't we? To me that sounds
just as bad.

Someone (I forget who at this late hour) gave several cogent arguments
that that's *exactly* what we want. Please see the prior discussion...

Right at the moment I think we have a consensus that we should use
SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not
say we've agreed to exactly those keywords, only that it's a good idea
to make them different from the outer-level BEGIN/END keywords.)

There was also some talk of offering commands based around the notion of
savepoints, but I'm not sure that we have a consensus on that yet.

Aren't subtransactions and their syntax defined by the SQL spec
somewhere?

Tom Lane wrote:

Mike Benoit <ipso@snappymail.ca> writes:

On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:

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

But then we're back to the application having to know if its in a
regular transaction or a sub-transaction aren't we? To me that sounds
just as bad.

Someone (I forget who at this late hour) gave several cogent arguments
that that's *exactly* what we want. Please see the prior discussion...

Right at the moment I think we have a consensus that we should use
SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not
say we've agreed to exactly those keywords, only that it's a good idea
to make them different from the outer-level BEGIN/END keywords.)

Either approach still needs some mechanism to clear the current stack of
transactions and subtransactions. That's why I was thinking ABORT ALL
and ROLLBACK ALL would be sufficient to cover that and be clear enough
to the user/programmer.

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

Question: with the new syntax, would issuing a BEGIN inside a already
started transaction result in an error?

My concern is about say, a pl/pgsql function that opened and closed a
transation. This could result in different behaviors depending if
called from within a transaction, which is not true of the old syntax.

Then again, since a statement is always transactionally wrapped, would
it be required to always issue SUBBEGIN if issued from within a
function? This would address my concern.

Merlin

On Fri, Jul 02, 2004 at 01:14:25PM -0400, Merlin Moncure wrote:

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

Question: with the new syntax, would issuing a BEGIN inside a already
started transaction result in an error?

Yes.

My concern is about say, a pl/pgsql function that opened and closed a
transation. This could result in different behaviors depending if
called from within a transaction, which is not true of the old syntax.

Then again, since a statement is always transactionally wrapped, would
it be required to always issue SUBBEGIN if issued from within a
function? This would address my concern.

Yes, I was thinking about this because the current code behaves wrong if
a BEGIN is issued and not inside a transaction block. So we'd need to
do something special in SPI -- not sure exactly what, but the effect
would be that the function can't issue BEGIN at all and can only issue
SUBBEGIN.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
A male gynecologist is like an auto mechanic who never owned a car.
(Carrie Snow)

Alvaro Herrera wrote:

On Fri, Jul 02, 2004 at 01:14:25PM -0400, Merlin Moncure wrote:

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

Question: with the new syntax, would issuing a BEGIN inside a already
started transaction result in an error?

Yes.

My concern is about say, a pl/pgsql function that opened and closed a
transation. This could result in different behaviors depending if
called from within a transaction, which is not true of the old syntax.

Then again, since a statement is always transactionally wrapped, would
it be required to always issue SUBBEGIN if issued from within a
function? This would address my concern.

Yes, I was thinking about this because the current code behaves wrong if
a BEGIN is issued and not inside a transaction block. So we'd need to
do something special in SPI -- not sure exactly what, but the effect
would be that the function can't issue BEGIN at all and can only issue
SUBBEGIN.

Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT
should be sufficient regardless of the level. If you are inside a
current transaction those commands start a new transaction inside of the
current transaction level, just like pushing on and popping off elements
on a stack.

I'm not trying to be argumentative, but the notation seems orthogonal to
the issue.

Some functions and procedures may not be called inside of transactions
or subtransactions. Having to start with a SUBBEGIN and
SUBCOMMIT/SUBABORT is equally problematic if you don't know where you
begin. Taking the extreme everything should be a SUBBEGIN and a
SUBCOMMIT/SUBABORT so why have BEGIN and END?

Unless you have some way to tell (by query) the state you are in is a
subtransaction and how many levels you are deep into the nested
transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs
the traditional BEGIN COMMIT/ABORT becomes nondeterministic.

Thomas Swan wrote:

Alvaro Herrera wrote:

Yes, I was thinking about this because the current code behaves wrong if
a BEGIN is issued and not inside a transaction block. So we'd need to
do something special in SPI -- not sure exactly what, but the effect
would be that the function can't issue BEGIN at all and can only issue
SUBBEGIN.

Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT
should be sufficient regardless of the level. If you are inside a
current transaction those commands start a new transaction inside of the
current transaction level, just like pushing on and popping off elements
on a stack.

How about this radical idea: Use SAVEPOINT to begin a subtransaction
and ROLLBACK TO SAVEPOINT to abort that subtransaction. Normally, in
Oracle, I would write code like:

SAVEPOINT foo;

<do work>

IF (error) THEN
ROLLBACK TO SAVEPOINT foo;
END IF;

Could we not treat a subtransaction as an "anonymous" savepoint
until savepoints are added? So the above in PostgreSQL would read:

SAVEPOINT;

<do work>

IF (error) THEN
ROLLBACK TO SAVEPOINT;
END IF;

My old SQL3 draft EBNF reads:

<savepoint statement> ::= SAVEPOINT <savepoint specifier>

<savepoint specifier> ::=
<savepoint name>
| <simple target specification>

<savepoint name> ::= <identifier>

and

<rollback statement> ::=
ROLLBACK [ WORK ] [ AND[ NO ] CHAIN ]
[ <savepoint clause> ]

<savepoint clause> ::=
TO SAVEPOINT <savepoint specifier>

Mike Mascari

On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote:

Alvaro Herrera wrote:

Then again, since a statement is always transactionally wrapped, would
it be required to always issue SUBBEGIN if issued from within a
function? This would address my concern.

Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT
should be sufficient regardless of the level. If you are inside a
current transaction those commands start a new transaction inside of the
current transaction level, just like pushing on and popping off elements
on a stack.

No, the first level is quite different from any other, and that's why it
should use a different syntax. Really any level above level 1 is not a
transaction at all; it's a unit that you can rollback independently but
nothing more; you can't commit it independently. I think a better term
than "subtransaction" or "nested transaction" is "rollback unit" or some
such.

Some functions and procedures may not be called inside of transactions
or subtransactions.

No. Some functions cannot be called inside a transaction block.
Whether you are or not inside a subtransaction within the transaction
block is not important. In fact, the application doesn't care what
nesting level it is in; the only thing that it cares about is if it is
in a transaction block or not.

Please note that I'm using the term "transaction block" and not
"transaction." The distinction is important because everything is
always inside a transaction, though it may be an implicit one. A
transaction block, on the other hand, is always an explicit thing.
And a subtransaction is also an explicit thing.

Unless you have some way to tell (by query) the state you are in is a
subtransaction and how many levels you are deep into the nested
transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs
the traditional BEGIN COMMIT/ABORT becomes nondeterministic.

The application always has to keep track if it is inside a transaction
block or not. This has always been true and it continues to be so.
Whether you are inside a subtransaction or not is not really important.
If you want to commit the whole transaction block just issue COMMIT, and
all levels will be committed. Similarly if you want to abort. But if
you want to retry a subtransaction which has just failed you better know
whether you are on a subtransaction or not ... I mean if the app
doesn't know that then it isn't using subtransactions, is it?

Knowing just the nesting level will not help you -- the app has to know
_what_ to retry. And if it isn't going to retry anything then there's
no point in using subtransactions at all.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando ma�ana llegue pelearemos segun lo que ma�ana exija" (Mowgli)

Alvaro Herrera wrote:

On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote:

Alvaro Herrera wrote:

Then again, since a statement is always transactionally wrapped, would
it be required to always issue SUBBEGIN if issued from within a
function? This would address my concern.

Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT
should be sufficient regardless of the level. If you are inside a
current transaction those commands start a new transaction inside of the
current transaction level, just like pushing on and popping off elements
on a stack.

No, the first level is quite different from any other, and that's why it
should use a different syntax. Really any level above level 1 is not a
transaction at all; it's a unit that you can rollback independently but
nothing more; you can't commit it independently. I think a better term
than "subtransaction" or "nested transaction" is "rollback unit" or some
such.

Some functions and procedures may not be called inside of transactions
or subtransactions.

No. Some functions cannot be called inside a transaction block.
Whether you are or not inside a subtransaction within the transaction
block is not important. In fact, the application doesn't care what
nesting level it is in; the only thing that it cares about is if it is
in a transaction block or not.

Please note that I'm using the term "transaction block" and not
"transaction." The distinction is important because everything is
always inside a transaction, though it may be an implicit one. A
transaction block, on the other hand, is always an explicit thing.
And a subtransaction is also an explicit thing.

This is the reason the outermost block is irrelevant to the point.
Inner transactions (including the implicit ones mentioned) commit only
if their parent transactions commit. If there is an implicit
begin/commit, then everything underneath should be subbegin/subcommit.
If it is sometimes implicit then the subbegin/begin state is
non-deterministic. Without the underlying or stack depth, it is
difficult to predict. In psql, autocommit (on/off) behavoir becomes a
little muddy if you go with the SUBBEGIN and SUBCOMMIT construct.

Below should BEGIN (1) be a SUBBEGIN or a BEGIN? Both examples would
give equivalent results.

--
BEGIN (implicit)
BEGIN (1)
BEGIN
SOMETHING
BEGIN
SOMETHING
ROLLBACK
ROLLBACK
COMMIT (1)
COMMIT (implicit)
--
BEGIN (1)
BEGIN
SOMETHING
BEGIN
SOMETHING
ROLLBACK
ROLLBACK
COMMIT (1)
--

Unless you have some way to tell (by query) the state you are in is a
subtransaction and how many levels you are deep into the nested
transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs
the traditional BEGIN COMMIT/ABORT becomes nondeterministic.

The application always has to keep track if it is inside a transaction
block or not. This has always been true and it continues to be so.
Whether you are inside a subtransaction or not is not really important.
If you want to commit the whole transaction block just issue COMMIT, and
all levels will be committed.

psql will tell me how deep I am in transactions?

Similarly if you want to abort. But if
you want to retry a subtransaction which has just failed you better know
whether you are on a subtransaction or not ... I mean if the app
doesn't know that then it isn't using subtransactions, is it?

That's an rather big assumption? It may not be the app, it may include
stored procedures and functions as well. Imagine a little function
called dance( ). Dance begins a transaction, does a little work, and
then aborts. If I am not in a transaction and I write dance as a
subtransaction then I have the problem and call it then I have a
problem. If I am in a transaction and I write dance as a transaction,
then I have a problem. There's no universal way to write the function,
without having to refer to an external state unless I make the scope
universal. Hence, SUBBEGIN and SUBCOMMIT are bad ideas.

Knowing just the nesting level will not help you -- the app has to know
_what_ to retry. And if it isn't going to retry anything then there's
no point in using subtransactions at all.

If you have the nesting level, then you know how many commits/rollbacks
to perform to get to an entrance state.

On Fri, Jul 02, 2004 at 03:32:12PM -0500, Thomas Swan wrote:

Alvaro Herrera wrote:

Please note that I'm using the term "transaction block" and not
"transaction." The distinction is important because everything is
always inside a transaction, though it may be an implicit one. A
transaction block, on the other hand, is always an explicit thing.
And a subtransaction is also an explicit thing.

This is the reason the outermost block is irrelevant to the point.
Inner transactions (including the implicit ones mentioned) commit only
if their parent transactions commit. If there is an implicit
begin/commit, then everything underneath should be subbegin/subcommit.
If it is sometimes implicit then the subbegin/begin state is
non-deterministic. Without the underlying or stack depth, it is
difficult to predict.

You can't have subtransactions inside an implicit transaction block, so
this answers all your concerns here I think. It just doesn't make
sense. How would you call a subtransaction in a implicit transaction?
Don't tell me to use BEGIN because that'd start an explicit transaction
block ...

In psql, autocommit (on/off) behavoir becomes a little muddy if you go
with the SUBBEGIN and SUBCOMMIT construct.

Au contraire ... autocommit is easier to support with separate syntax
AFAICT.

psql will tell me how deep I am in transactions?

Yes, there should be a way to know this, if only for showing it in the
prompt. It's not there at present.

Similarly if you want to abort. But if you want to retry a
subtransaction which has just failed you better know whether you are
on a subtransaction or not ... I mean if the app doesn't know that
then it isn't using subtransactions, is it?

That's an rather big assumption? It may not be the app, it may include
stored procedures and functions as well.

I said in some other thread that a function can not call BEGIN, only
SUBBEGIN (there is a reason besides this one, and it is that it just
doesn't work to use BEGIN in a function when not in a transaction block
-- you can try it with current sources).

At this point you can claim that in this case you would not be able to
call a function that uses subtransactions when not in a transaction
block; that's why we need to automatically start a transaction block to
call a function, if not already in one.

If you have the nesting level, then you know how many commits/rollbacks
to perform to get to an entrance state.

Why bother? Just issue one and you are done.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hern�ndez-Novich)

On Fri, Jul 02, 2004 at 05:30:49PM -0400, Alvaro Herrera wrote:

You can't have subtransactions inside an implicit transaction block, so

Haven't been following this thread closely, but just my 2 cents...

If you collate queries using the semicolon, AFAIK the whole thing is
executed as a single implicit transaction (if not in an explicit one
already, of course). So is there anyone stopping a user from executing

BEGIN ; UPDATE ... ; COMMIT

?

Jeroen

On Fri, Jul 02, 2004 at 11:51:01PM +0200, Jeroen T. Vermeulen wrote:

On Fri, Jul 02, 2004 at 05:30:49PM -0400, Alvaro Herrera wrote:

You can't have subtransactions inside an implicit transaction block, so

Haven't been following this thread closely, but just my 2 cents...

If you collate queries using the semicolon, AFAIK the whole thing is
executed as a single implicit transaction (if not in an explicit one
already, of course).

Oh, right, I forgot about this one ... Will think about it (it should
be forbidden).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendi�nse", y el computador dir� "especifique el tipo de ciervo"
(Jason Alexander)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

You can't have subtransactions inside an implicit transaction block,

It would be folly to design on that assumption. We *will* have that
situation just as soon as plpgsql allows creating subtransactions
(which I trust you'll agree will happen soon). All you have to do
is call such a function from a bare SELECT. I do not think you'll
be able to legislate that people must say BEGIN first.

regards, tom lane

On Fri, Jul 02, 2004 at 07:43:47PM -0400, Tom Lane wrote:

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

You can't have subtransactions inside an implicit transaction block,

It would be folly to design on that assumption. We *will* have that
situation just as soon as plpgsql allows creating subtransactions
(which I trust you'll agree will happen soon).

It is allowed already (this is why I hacked SPI in the first place). In
fact, it can easily cause a server crash. Try this function:

create function crashme() returns int language plpgsql as '
begin
start transaction;
commit transaction;
return 1;
end;
';

Try running it without starting a transaction; the server crashes. If
you run it inside a transaction block, there is no crash.

The reason this happens is that the first START TRANSACTION starts the
transaction block (since we are already in a transaction this is a no-op
as far as the transaction is concerned), and the commit ends it, blowing
the function state out of the water. This does not happen within a
transaction block, and the nesting is OK (i.e. you have to issue one and
only one COMMIT command to end the transaction block).

This shows that the first BEGIN is different from any other: the first
is some kind of no-op (the transaction starts regardless of it), while
any subsequent BEGIN actually starts a subtransaction.

Another thing to try is

create function dontcrashme() returns int language plpgsql as '
begin
start transaction;
start transaction;
commit transaction;
return 1;
end;
';

Obviously this doesn't crash regardless of whether you are inside a
transaction block or not. But you have to issue a COMMIT after the
function is called to return to a sane state.

What I'd like to do is start the transaction block before the function
is called if we are not in a transaction block. This would mean that
when the function calls BEGIN it won't be the first one -- it will
actually start a subtransaction and will be able to end it without harm.
I think this can be done automatically at the SPI level.

One situation I don't know how to cope with is a multiquery statement,
as pointed out by Jeroem.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Al principio era UNIX, y UNIX habl� y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".

Alvaro Herrera wrote:

On Fri, Jul 02, 2004 at 07:43:47PM -0400, Tom Lane wrote:

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

You can't have subtransactions inside an implicit transaction block,

It would be folly to design on that assumption. We *will* have that
situation just as soon as plpgsql allows creating subtransactions
(which I trust you'll agree will happen soon).

It is allowed already (this is why I hacked SPI in the first place). In
fact, it can easily cause a server crash. Try this function:

create function crashme() returns int language plpgsql as '
begin
start transaction;
commit transaction;
return 1;
end;
';

Try running it without starting a transaction; the server crashes. If
you run it inside a transaction block, there is no crash.

The reason this happens is that the first START TRANSACTION starts the
transaction block (since we are already in a transaction this is a no-op
as far as the transaction is concerned), and the commit ends it, blowing
the function state out of the water. This does not happen within a
transaction block, and the nesting is OK (i.e. you have to issue one and
only one COMMIT command to end the transaction block).

This shows that the first BEGIN is different from any other: the first
is some kind of no-op (the transaction starts regardless of it), while
any subsequent BEGIN actually starts a subtransaction.

Another thing to try is

create function dontcrashme() returns int language plpgsql as '
begin
start transaction;
start transaction;
commit transaction;
return 1;
end;
';

Obviously this doesn't crash regardless of whether you are inside a
transaction block or not. But you have to issue a COMMIT after the
function is called to return to a sane state.

What I'd like to do is start the transaction block before the function
is called if we are not in a transaction block. This would mean that
when the function calls BEGIN it won't be the first one -- it will
actually start a subtransaction and will be able to end it without harm.
I think this can be done automatically at the SPI level.

One situation I don't know how to cope with is a multiquery statement,
as pointed out by Jeroem.

Please tell me there is some sanity in this. If I follow you
correctly, at no point should anyone be able to issue an explicit
begin/end because they are already in an explicit/implicit transaction
by default... How is the user/programmer to know when this is the case?

Alvaro Herrera <alvherre@dcc.uchile.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