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.

Thomas


Thu, 01 Jul 2004 14:01:37 -0500
Thomas Swan <tswan@idigx.com>
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)


Thu, 1 Jul 2004 18:38:41 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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>


Thu, 01 Jul 2004 16:47:09 -0700
Mike Benoit <ipso@snappymail.ca>
Re: Nested Transactions, Abort All
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)


Thu, 1 Jul 2004 21:56:02 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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

Fri, 02 Jul 2004 00:14:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Nested Transactions, Abort All 
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?


Thu, 01 Jul 2004 22:36:29 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: Nested Transactions, Abort All
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.

>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
>  
>


Fri, 02 Jul 2004 11:18:08 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All

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


03 Jul 2004 10:25:20 -0400
Greg Stark <gsstark@mit.edu>
Re: Nested Transactions, Abort All
Greg Stark <gsstark@mit.edu> writes:
> This seems like a irregularity in the API that makes sense only from an
> implementation point of view.

You are attacking a straw man.  This does *not* "make sense from an
implementation point of view" --- it's easier to have just one command
(and in fact that is what is in CVS tip).  The proposal to use different
commands was advanced on the grounds that it's a more user-friendly API.

			regards, tom lane

Sat, 03 Jul 2004 11:28:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Nested Transactions, Abort All 
Greg Stark wrote:
> 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.

I think it's vital that any use of the existing (pre-7.5) syntax for 
COMMIT/ROLLBACK/ABORT results in all transaction state being cleared, 
for compatibility with older applications.

Consider JDBC's Connection.commit() and Connection.rollback() methods. 
They need to deal in terms of the top-level transaction: connection 
pools and similar are going to issue rollback() and expect all 
transaction state to be cleaned up, regardless of what the pool's client 
has done with the connection. The connection pool code is unlikely to be 
aware of subtransactions -- JDBC has no equivalent concept (it has 
savepoints, but that's it).

If ROLLBACK always affects the top-level transaction, the JDBC driver's 
job is simple: Connection.rollback() always issues ROLLBACK. If you need 
some other syntax to get a top-level rollback, the driver's job gets 
messier:

   if we have at least a 7.5 server:
      issue ROLLBACK ALL
   else:
      issue ROLLBACK

and older drivers which always issue ROLLBACK are going to break in 
nonobvious ways if their applications start using subtransactions.

This seems like a gratuitous incompatibility to introduce. I'd expect 
other clients that aren't aware of subtransactions to stumble on this too.

-O

Mon, 05 Jul 2004 14:58:13 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Nested Transactions, Abort All