Thread: Nested Transactions, Abort All

Nested Transactions, Abort All

From
Thomas Swan
Date:
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



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
Mike Benoit
Date:
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>



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
Tom Lane
Date:
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


Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
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?



Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
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
>  
>



Re: Nested Transactions, Abort All

From
"Merlin Moncure"
Date:
> 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


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
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.




Re: Nested Transactions, Abort All

From
Mike Mascari
Date:
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








Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
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.




Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
"Jeroen T. Vermeulen"
Date:
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



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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)



Re: Nested Transactions, Abort All

From
Tom Lane
Date:
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


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
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".



Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
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?




Re: Nested Transactions, Abort All

From
Greg Stark
Date:
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



Re: Nested Transactions, Abort All

From
Tom Lane
Date:
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


Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
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


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote:
> Alvaro Herrera wrote:

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

I'm not sure I understand you.  Of course you can issue begin/end.  What
you can't do is issue begin/end inside a function -- you always use
subbegin/subcommit in that case.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Mon, 5 Jul 2004, Alvaro Herrera wrote:

> > 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?
> 
> I'm not sure I understand you.  Of course you can issue begin/end.  What
> you can't do is issue begin/end inside a function -- you always use
> subbegin/subcommit in that case.

I've not understood why we need new tokens for this case. Maybe you've 
explained it somewhere that I've missed. But surely the server know if you 
are in a transaction or not, and can differentiate on the first BEGIN and 
the next BEGIN.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote:
> > Alvaro Herrera wrote:
> 
> > >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.
> >
> > 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?
> 
> I'm not sure I understand you.  Of course you can issue begin/end.  What
> you can't do is issue begin/end inside a function -- you always use
> subbegin/subcommit in that case.

And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call
inside from an explicit transaction, it will work because the call
itself is its own implicit transaction, right?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Tue, Jul 06, 2004 at 11:37:18AM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote:
> > > Alvaro Herrera wrote:
> > 
> > > >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.
> > >
> > > 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?
> > 
> > I'm not sure I understand you.  Of course you can issue begin/end.  What
> > you can't do is issue begin/end inside a function -- you always use
> > subbegin/subcommit in that case.
> 
> And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call
> inside from an explicit transaction, it will work because the call
> itself is its own implicit transaction, right?

Right.  Note that this doesn't work with the current code -- in fact you
can cause a server crash easily.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem."  (Tom Lane)



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote:
> On Mon, 5 Jul 2004, Alvaro Herrera wrote:
> 
> > > 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?
> > 
> > I'm not sure I understand you.  Of course you can issue begin/end.  What
> > you can't do is issue begin/end inside a function -- you always use
> > subbegin/subcommit in that case.
> 
> I've not understood why we need new tokens for this case. Maybe you've 
> explained it somewhere that I've missed. But surely the server know if you 
> are in a transaction or not, and can differentiate on the first BEGIN and 
> the next BEGIN.

I think the best argument for this is that we need a command to abort
the whole transaction tree, and another to commit the whole transaction
tree.  Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END),
because the spec says they work like that and it would be hell for an
interface like JDBC if they didn't.  So it's out of the picture to use
those commands to end a subtransaction.

Now, it's clear we need new commands to end a subtransaction.  Do we
also want a different command for begin?  I think so, just to be
consistent.

Conclusion: we need a different syntax.  So we invent an extension.  

We could use BEGIN NESTED for starting a subtransaction, roll it back
with ROLLBACK NESTED or some such, and commit with COMMIT NESTED.  But I
like SUBBEGIN etc best, and no one had an opinion when I asked.  So the
current code has SUBBEGIN, SUBCOMMIT, SUBABORT.  If people prefer
another syntax, then we can have a vote or core hackers can choose -- I
don't care what the syntax is, but it has to be different from BEGIN,
COMMIT, ROLLBACK.

We can later implement savepoints, which will have "SAVEPOINT foo" and
"ROLLBACK TO foo" as interface.  (Note that a subtransaction is slightly
different from a savepoint, so we can't use ROLLBACK TO <foo> in
subtransactions because that has a different meaning in savepoints).

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La rebeldía es la virtud original del hombre" (Arthur Schopenhauer)



Re: Nested Transactions, Abort All

From
Stephen Frost
Date:
* Alvaro Herrera (alvherre@dcc.uchile.cl) wrote:
> We could use BEGIN NESTED for starting a subtransaction, roll it back
> with ROLLBACK NESTED or some such, and commit with COMMIT NESTED.  But I
> like SUBBEGIN etc best, and no one had an opinion when I asked.  So the
> current code has SUBBEGIN, SUBCOMMIT, SUBABORT.  If people prefer

Just to be pedantic and talking about consistency-
Why SUBABORT instead of SUBROLLBACK?
Stephen

Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Tue, Jul 06, 2004 at 12:49:46PM -0400, Stephen Frost wrote:
> * Alvaro Herrera (alvherre@dcc.uchile.cl) wrote:
> > We could use BEGIN NESTED for starting a subtransaction, roll it back
> > with ROLLBACK NESTED or some such, and commit with COMMIT NESTED.  But I
> > like SUBBEGIN etc best, and no one had an opinion when I asked.  So the
> > current code has SUBBEGIN, SUBCOMMIT, SUBABORT.  If people prefer
> 
> Just to be pedantic and talking about consistency- 
> Why SUBABORT instead of SUBROLLBACK?

Just because it's ugly and too long ... I think the standard spelling is
ROLLBACK, and ABORT is a Postgres extension.  Since nested xacts are a
Postgres extension, we might as well extend our own syntax :-)

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



Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
On Tue, 2004-07-06 at 10:25, Alvaro Herrera wrote:
> On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote:
> > On Mon, 5 Jul 2004, Alvaro Herrera wrote:
> > 
> > > > 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?
> > > 
> > > I'm not sure I understand you.  Of course you can issue begin/end.  What
> > > you can't do is issue begin/end inside a function -- you always use
> > > subbegin/subcommit in that case.
> > 
> > I've not understood why we need new tokens for this case. Maybe you've 
> > explained it somewhere that I've missed. But surely the server know if you 
> > are in a transaction or not, and can differentiate on the first BEGIN and 
> > the next BEGIN.
> 
> I think the best argument for this is that we need a command to abort
> the whole transaction tree, and another to commit the whole transaction
> tree.  Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END),
> because the spec says they work like that and it would be hell for an
> interface like JDBC if they didn't.  So it's out of the picture to use
> those commands to end a subtransaction.

Why not rollback all or commit all?

I really really don't like subbegin and subcommit.  I get the feeling
they'll cause more problems we haven't foreseen yet, but I can't put my
finger on it.  They just don't feel like "postgresql" to me.  I'd rather
see extra syntax to handle exceptions, like rollback all or whatnot,
than subbegin et. al.

> 
> Now, it's clear we need new commands to end a subtransaction.  Do we
> also want a different command for begin?  I think so, just to be
> consistent.

Sorry, but I respectfully disagree that it's clear.  



Re: Nested Transactions, Abort All

From
Greg Stark
Date:
"Scott Marlowe" <smarlowe@qwest.net> writes:

> Why not rollback all or commit all?
> 
> I really really don't like subbegin and subcommit.  I get the feeling
> they'll cause more problems we haven't foreseen yet, but I can't put my
> finger on it.  

Well I've already pointed out one problem. It makes it impossible to write
generic code or reuse existing code and embed it within a transaction. Code
meant to be a nested transaction within a larger transaction becomes
non-interchangeable with code meant to be run on its own.

I also have a different issue. The behaviour I'm expecting with most drivers
will be to start a transaction immediately, and run every query within a
subtransaction. This is what we've discussed previously with psql, but for the
same reasons previously discussed I expect drivers to adopt the same approach,
at least when not in autocommit mode. The goal would be to allow the
application to control what happens when a given query returns an error and
not force the application to roll the entire transaction back.

This means the user can't use "BEGIN" or "END" at all himself. Since the
driver would already have initiated a transaction itself. The *only*
user-visible commands would become these awkward (and frankly, silly-sounding)
"SUBBEGIN" and "SUBEND".

I have an related question though. Will there be a out of band protocol method
for controlling transaction status? If the v3 protocol allows the transaction
status to be manipulated in binary messages that don't interact with user
queries then a driver would still be able to reliably start and end
transactions and nested transactions. If that were the case I guess I wouldn't
care since a driver could then implement an external API that hid the
irregularity of SUBfoo from the user and provided a consistent ->begin()
->end(). The driver could emulate this by inserting SUBfoo commands into the
stream but then it would risk being subverted by user commands.

-- 
greg



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Tue, 6 Jul 2004, Alvaro Herrera wrote:

> We can later implement savepoints, which will have "SAVEPOINT foo" and
> "ROLLBACK TO foo" as interface.  (Note that a subtransaction is slightly
> different from a savepoint, so we can't use ROLLBACK TO <foo> in
> subtransactions because that has a different meaning in savepoints).

What is the semantic difference?

In my eye the subtransactions and the savepoints are basically the 
same thing except the label that is used. If that is the only difference?
why are we implementing our own extension for subtransactions instead of 
implementing this standard feature.

Of course the label stuff is a little more complicated, but all the really
hard parts should be the same as what have already been done. The most 
naive implementation of the labels is to have a mapping from a label to 
the number of subcommit (for RELEASE SAVEPOINT) or subrolllbacks (for 
ROLLBACK TO SAVEPOINT) to execute.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Dennis Bjorklund wrote:
> On Tue, 6 Jul 2004, Alvaro Herrera wrote:
> 
> 
>>We can later implement savepoints, which will have "SAVEPOINT foo" and
>>"ROLLBACK TO foo" as interface.  (Note that a subtransaction is slightly
>>different from a savepoint, so we can't use ROLLBACK TO <foo> in
>>subtransactions because that has a different meaning in savepoints).
> 
> 
> What is the semantic difference?

Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" 
is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back 
subtransaction foo and all children; start new subtransaction foo'.

-O


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Wed, 7 Jul 2004, Oliver Jowett wrote:

> Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" 
> is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back 
> subtransaction foo and all children; start new subtransaction foo'.

If that is all there is, I much rather see this standard interface then a
pg extension.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Dennis Bjorklund wrote:
> On Wed, 7 Jul 2004, Oliver Jowett wrote:
> 
> 
>>Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" 
>>is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back 
>>subtransaction foo and all children; start new subtransaction foo'.
> 
> 
> If that is all there is, I much rather see this standard interface then a
> pg extension.

So how do you propose supporting simple rollback of a subtransaction? It 
seems like an extension regardless of how it's done.

-O


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Wed, 7 Jul 2004, Oliver Jowett wrote:

> So how do you propose supporting simple rollback of a subtransaction? It 
> seems like an extension regardless of how it's done.

If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
foo; followed by a RELEASE SAVEPOINT foo; 

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Dennis Bjorklund wrote:
> On Wed, 7 Jul 2004, Oliver Jowett wrote:
> 
> 
>>So how do you propose supporting simple rollback of a subtransaction? It 
>>seems like an extension regardless of how it's done.
> 
> 
> If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
> foo; followed by a RELEASE SAVEPOINT foo; 

Ugh.. nasty syntax and an extra empty transaction.

Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would 
seem to always require a name.

One of the use cases for subtransactions was to avoid rollback of the 
entire transaction if there's an error in a single command -- you wrap 
each command in a subtransaction and roll it back if it fails. If we 
only have SAVEPOINT syntax this looks like:
  -- Success case  SAVEPOINT s_12345   INSERT INTO foo(...) VALUES (...)  RELEASE SAVEPOINT s_12345
  -- Error case  SAVEPOINT s_12346   INSERT INTO foo(...) VALUES (...)  ROLLBACK TO SAVEPOINT s_12346  RELEASE
SAVEPOINTs_12346
 
  -- Repeat ad nauseam

This is pretty ugly. Given that the underlying mechanism is nested 
subtransactions, why should it be necessary to jump through those sort 
of hoops to gain access to them?

If you don't like adding extra commands, what about extending the 
standard transaction control commands ("BEGIN NESTED" etc) instead?

-O


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Wed, 7 Jul 2004, Oliver Jowett wrote:

> > If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
> > foo; followed by a RELEASE SAVEPOINT foo; 
> 
> Ugh.. nasty syntax and an extra empty transaction.

If you translate it directly using only the primitives of the current 
subbegin/subabort, yes. But that is not the only way to implement it. And 
even if that was the first implementation due to not having time to make 
it better before 7.5, then I still prefer a standard syntax that can be 
improved then a non standard feature to be maintained for all future.

This is about the API to present to the user. The savepoint syntax is
standard, if we should invent our own way it should be for some real
benefit.

> Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would 
> seem to always require a name.

Yes, it does. But surely they can be nested so an inner use of name foo 
hides an outer use of name foo. I'm not pretending to know all about the 
standard savepoints, so I just assume they can be nested.

> One of the use cases for subtransactions was to avoid rollback of the 
> entire transaction if there's an error in a single command -- you wrap 
> each command in a subtransaction and roll it back if it fails. If we 
> only have SAVEPOINT syntax this looks like:
> 
>    -- Success case
>    SAVEPOINT s_12345
>     INSERT INTO foo(...) VALUES (...)
>    RELEASE SAVEPOINT s_12345
> 
>    -- Error case
>    SAVEPOINT s_12346
>     INSERT INTO foo(...) VALUES (...)
>    ROLLBACK TO SAVEPOINT s_12346
>    RELEASE SAVEPOINT s_12346
> 
>    -- Repeat ad nauseam
>
> This is pretty ugly. Given that the underlying mechanism is nested 
> subtransactions,

So you do not want to use the standard syntax in order to save some tokens
in the source?

Also notice that the first and last statement is the same no matter if you
want to rollback or not. So it would be something like (with a nicer
savepoint name then yours):

SAVEPOINT insert;
  INSERT INTO ....
  ... possible more work ...
  if (some_error)     ROLLBACK TO SAVEPOINT insert;

RELEASE SAVEPOINT insert;

I really don't see this as anything ugly with this. Maybe it doesn't fit 
the current implementation, then lets change the implementation and not 
just make an extension that fits a implementation.

> If you don't like adding extra commands, what about extending the 
> standard transaction control commands ("BEGIN NESTED" etc) instead?

I'd like to use the ansi standard and hopefully portable syntax. I don't
see any real gains by having our own syntax. If the goal is just to save 
some tokens I definetly see no reason. There might still be something more 
to subtransactions, but I really have not seen it.

At the very least if we add extensions I would like to have a clear and 
stated reason why it should be used instead of the standard feature. Every 
time we add some syntax it has to be maintained forever and we lock in 
users into postgresql. Something I don't like.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Dennis Bjorklund wrote:

>>Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would 
>>seem to always require a name.
> 
> Yes, it does. But surely they can be nested so an inner use of name foo 
> hides an outer use of name foo. I'm not pretending to know all about the 
> standard savepoints, so I just assume they can be nested.

The specs appear to say that reuse of a savepoint name moves the name 
rather than hiding it. There's also a concept of a savepoint level which 
seems to be essentially a namespace for savepoints, and provision for 
entering a new savepoint level during a call to a SQL function.

> I'd like to use the ansi standard and hopefully portable syntax. I don't
> see any real gains by having our own syntax. If the goal is just to save 
> some tokens I definetly see no reason. There might still be something more 
> to subtransactions, but I really have not seen it.

My concern is that if we are building savepoints on top of nested 
subtransactions -- which is the approach so far -- then treating that 
system as something that only provides savepoints is counterproductive.

Consider:
  SAVEPOINT point1    -- work 1    -- maybe ROLLBACK TO point1  SAVEPOINT point2    -- work 2    -- maybe ROLLBACK TO
point2 SAVEPOINT point1    -- work 3    -- maybe ROLLBACK TO point1  SAVEPOINT point2    -- work 4    -- maybe ROLLBACK
TOpoint2
 
  -- repeat ad nauseam

On the surface this looks cheap if you treat the transaction model as 
one flat transaction with N savepoints (which is what SAVEPOINT seems to  be about doing, looking at it independent of
animplementation) -- 
 
there are only two savepoints active at any particular point. But if the 
underlying model is actually nested transactions, you are going to end 
up with a very large number of active nested transactions, since at the 
point the server sees the reuse of 'point1' it's too late to commit the 
transaction maintaining that savepoint safely.

This can be fixed by explicit RELEASE SAVEPOINTs after each block of 
work, but it's not obvious from the savepoint model why this is needed 
-- you only have 2 savepoints active anyway!

Also:
  SAVEPOINT point1  DECLARE CURSOR foocursor FOR SELECT * from footable    -- work  RELEASE SAVEPOINT point1  FETCH
FORWARD10 FROM foocursor  -- oops, foocursor is no longer open
 

That behaviour just doesn't fit into the 
flat-transaction-with-savepoints model at all.

I guess the question is: are we adding a nested transaction facility or 
a savepoint facility? It seems to me we're doing the former, and the 
savepoint syntax plus mostly-standard savepoint behaviour is just 
compatibility icing. If that's the case, I'd prefer a syntax that 
reflects the nested-transaction nature of the beast.

-O


Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
On Wed, 2004-07-07 at 00:16, Dennis Bjorklund wrote:
> On Tue, 6 Jul 2004, Alvaro Herrera wrote:
> 
> > We can later implement savepoints, which will have "SAVEPOINT foo" and
> > "ROLLBACK TO foo" as interface.  (Note that a subtransaction is slightly
> > different from a savepoint, so we can't use ROLLBACK TO <foo> in
> > subtransactions because that has a different meaning in savepoints).
> 
> What is the semantic difference?

One is in the SQL spec?

For that reason alone, we should probably eventually have the savepoint
syntax work.




Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
On Tue, 2004-07-06 at 23:36, Greg Stark wrote:
> "Scott Marlowe" <smarlowe@qwest.net> writes:
> 
> > Why not rollback all or commit all?
> > 
> > I really really don't like subbegin and subcommit.  I get the feeling
> > they'll cause more problems we haven't foreseen yet, but I can't put my
> > finger on it.  
> 
> Well I've already pointed out one problem. It makes it impossible to write
> generic code or reuse existing code and embed it within a transaction. Code
> meant to be a nested transaction within a larger transaction becomes
> non-interchangeable with code meant to be run on its own.

Would a rollback N / abort N where N is the number of levels to rollback
/ abort work?  




Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
Scott Marlowe wrote:

>On Tue, 2004-07-06 at 23:36, Greg Stark wrote:
>  
>
>>"Scott Marlowe" <smarlowe@qwest.net> writes:
>>
>>    
>>
>>>Why not rollback all or commit all?
>>>
>>>I really really don't like subbegin and subcommit.  I get the feeling
>>>they'll cause more problems we haven't foreseen yet, but I can't put my
>>>finger on it.  
>>>      
>>>
>>Well I've already pointed out one problem. It makes it impossible to write
>>generic code or reuse existing code and embed it within a transaction. Code
>>meant to be a nested transaction within a larger transaction becomes
>>non-interchangeable with code meant to be run on its own.
>>    
>>
>
>Would a rollback N / abort N where N is the number of levels to rollback
>/ abort work?  
>
>  
>
Only, if you know the number of levels you are deep in the transaction.  

"ROLLBACK n" and "ROLLBACK ALL" together would be good alternatives to 
unwind nested transaction.  Perhaps a function for 
pg_transaction_nested_level( ) or a pg_transaction_nested_level variable 
could help in this.

Again, these are just opinions.




Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Alvaro, Hackers:

I've been giving this some thought.   Here's what I came up with:

We should NOT use the savepoint syntax.   Alvaro's Nested Transactions are not 
savepoints, they don't meet the spec, and they don't behave the same.  Using 
standard syntax for a non-standard feature will, in my opinion, cause more 
confusion than using extension syntax for what is, after all, a 
PostgreSQL-specific feature.

HOWEVER, other databases already have nested transactions.   We could do worse 
than to imitate their syntax; since the syntax we use is arbitrary, we might 
as well pick syntax which minimizes the pain of porting applications.   Of 
the other databases, the most important to imitate for this reason are of 
couse SQL Server and Oracle, since those to cover some 80% of DBAs.

However, Oracle does not support 

SQL Server uses:
Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  Not supported
Rollback inner transaction:  Not supported
Rollback all transanctions:  ROLLBACK { TRANSACTION | WORK }

Please note that, according to the above, MSSQL does not really support nested 
transactions; the inner transactions *cannot* be rolled back, making them 
useless.  There are numerous online discussions about this.

Sybase uses identical syntax, except that Sybase supports Savepoints via an 
extension of the BEGIN/COMMIT syntax:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN TRANSACTION _name_
Commit inner transaction:  COMMIT { TRANSACTION _name_ }
Commit all transactions:  Not supported
Rollback inner transaction:  ROLLBACK TRANSACTION _name_
Rollback all transanctions:  ROLLBACK { TRANSACTION | WORK }

This means that we CANNOT maintain compatibility with other databases without 
supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
I would propose the following syntax:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  COMMIT ALL
Rollback inner transaction:  ROLLBACK { TRANSACTION }
Rollback all transanctions:  ROLLBACK ALL

This would have the flaw of appearing to support SQL Server syntax, while 
actually having a different effect (that is, SQL Server programmers would 
assume that a ROLLBACK would abort everything, but it wouldn't).   If we 
wanted to maintain compatibility in this regard, for easy porting of SQL 
Server applications, we would:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  COMMIT ALL
Rollback inner transaction:  ROLLBACK NESTED
Rollback all transanctions:  ROLLBACK { TRANSACTION }

... but this puts us in the bad position of supporting somebody else's 
logically inconsistent syntax.    Thoughts?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:

> This means that we CANNOT maintain compatibility with other databases without 
> supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
> I would propose the following syntax:
> 
> Begin main transaction:   BEGIN { TRANSACTION | WORK }
> Begin inner transaction:  BEGIN { TRANSACTION | WORK }
> Commit inner transaction:  COMMIT { TRANSACTION | WORK }
> Commit all transactions:  COMMIT ALL
> Rollback inner transaction:  ROLLBACK { TRANSACTION }
> Rollback all transanctions:  ROLLBACK ALL

We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
defined per spec to end the transaction.  So they have to end the
transaction.

Keep in mind that a nested transaction _is not_ a transaction.  You
cannot commit it; it doesn't behave atomically w.r.t. other concurrent
transactions.  It is not a transaction in the SQL meaning of a
transaction.

So, when I say "it has to end the transaction" it cannot just end the
current nested transaction.  It has to end the _real_ transaction.


My proposal would be:

1. Begin main transaction: BEGIN { TRANSACTION | WORK }
2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }

4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }


1, 2 and 3 are not negotiable.  4, 5 and 6 are.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)


Re: Nested Transactions, Abort All

From
Barry Lind
Date:
Alvaro,
> My proposal would be:>> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }> 2. Commit main (all) transaction:
COMMIT{ TRANSACTION | WORK }> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }>> 4. Begin inner
transaction:BEGIN NESTED { TRANSACTION | WORK }> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }> 6.
Rollbackinner transaction: ROLLBACK NESTED { TRANSACTION }>
 

I agree with your 1,2 and 3, for the reasons you specify.

I don't like your proposal for 5, because using the keyword COMMIT 
implies something that really isn't true IMHO.  This is due to the fact 
as you point out subtransactions aren't really transactions.  So when 
you 'commit' a subtransaction you are not making the changes permanent 
like a regular transaction.  Instead you are saying these changes are OK 
and the real transaction gets to decide if these changes should be 
committed (or not).  It is only the real transaction that ever does a 
COMMIT (i.e. makes the changes permanent for others to see).  IMHO it is 
for these reasons that the standard SAVEPOINT syntax doesn't have a 
concept of committing a savepoint, only of rolling back to a savepoint.

thanks,
--Barry


Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> 
> 
>>This means that we CANNOT maintain compatibility with other databases without 
>>supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
>>I would propose the following syntax:
>>
>>Begin main transaction:   BEGIN { TRANSACTION | WORK }
>>Begin inner transaction:  BEGIN { TRANSACTION | WORK }
>>Commit inner transaction:  COMMIT { TRANSACTION | WORK }
>>Commit all transactions:  COMMIT ALL
>>Rollback inner transaction:  ROLLBACK { TRANSACTION }
>>Rollback all transanctions:  ROLLBACK ALL
> 
> 
> We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction.  So they have to end the
> transaction.
> 
> Keep in mind that a nested transaction _is not_ a transaction.  You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions.  It is not a transaction in the SQL meaning of a
> transaction.
> 
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction.  It has to end the _real_ transaction.
> 
> 
> My proposal would be:
> 
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
> 
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
> 
> 
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.
> 



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> 
> > This means that we CANNOT maintain compatibility with other databases without 
> > supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
> > I would propose the following syntax:
> > 
> > Begin main transaction:   BEGIN { TRANSACTION | WORK }
> > Begin inner transaction:  BEGIN { TRANSACTION | WORK }
> > Commit inner transaction:  COMMIT { TRANSACTION | WORK }
> > Commit all transactions:  COMMIT ALL
> > Rollback inner transaction:  ROLLBACK { TRANSACTION }
> > Rollback all transanctions:  ROLLBACK ALL
> 
> We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction.  So they have to end the
> transaction.
> 
> Keep in mind that a nested transaction _is not_ a transaction.  You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions.  It is not a transaction in the SQL meaning of a
> transaction.
> 
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction.  It has to end the _real_ transaction.
> 
> 
> My proposal would be:
> 
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
> 
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
> 
> 
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.

Let me jump in on this.

The initial proposal from Alvaro was to do SUBBEGIN/SUBCOMMIT.  This has
the advantage of allowing BEGIN/COMMIT to commit the entire transaction,
and it is a keyword we can use in plpgsql that doesn't confuse
BEGIN/END.

The disadvantages are:
o  adds prefix to keyword (SUB) which we don't do other placeso  doesn't work well with other xact synonyms like
BEGIN/ENDor   START TRANSACTION/COMMIT TRANSACTION.
 

Alvaro wants BEGIN/COMMIT to remain spec-compliant and commit the entire
transaction.  One idea was to do BEGIN NESTED/COMMIT NESTED, but does
that allow plpgsql to use it?  If not, it seems pretty useless.  Imagine:
BEGIN    NESTED = 3;

or something like that.

As far as savepoints, yes, we should support them.  Josh is saying our
implementation isn't 100% spec compliant.  In what way does it differ?

As far as implementing only savepoints, look at this:

BEGIN;BEGIN;INSERT INTO ...;COMMIT;BEGIN;INSERT INTO ...;COMMIT;BEGIN;INSERT INTO ...;COMMIT;

With savepoints, it looks pretty strange:
BEGIN;SAVEPOINT x1;INSERT INTO ...;SAVEPOINT x2;INSERT INTO ...;SAVEPOINT x3;INSERT INTO ...;

or with RELEASE:

BEGIN;SAVEPOINT x1;INSERT INTO ...;RELEASE SAVEPOINT x1;SAVEPOINT x1;INSERT INTO ...;RELEASE SAVEPOINT x1;SAVEPOINT
x1;INSERTINTO ...;RELEASE SAVEPOINT x1;
 

Yea, I guess it works.  With nested transactions, the SQL mimics the
nested structure of many application languages, while savepoints look
like an add-on to SQL.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Simon Riggs
Date:
ISTM - my summary would be
1. We seem to agree we should support SAVEPOINTs

2. We seem to agree that BEGIN/COMMIT should stay unchanged...

> With savepoints, it looks pretty strange:
>     
> BEGIN;
>     SAVEPOINT x1;
>     INSERT INTO ...;
>     SAVEPOINT x2;
>     INSERT INTO ...;
>     SAVEPOINT x3;
>     INSERT INTO ...;
> 

This isn't how you would use SAVEPOINTs...look at this...

BEGIN        display one screen to user - book the flightINSERT INTO ...INSERT INTO ...UPDATE ...SAVEPOINT
displayanother related screen - book the hotelINSERT INTODELETEUPDATEUPDATESAVEPOINT        offer confirmation screen
 
COMMIT (or ROLLBACK)

RELEASE SAVEPOINT isn't used that often...

> or with RELEASE:
> 
> BEGIN;
>     SAVEPOINT x1;
>     INSERT INTO ...;
>     RELEASE SAVEPOINT x1;
>     SAVEPOINT x1;
>     INSERT INTO ...;
>     RELEASE SAVEPOINT x1;
>     SAVEPOINT x1;
>     INSERT INTO ...;
>     RELEASE SAVEPOINT x1;
> 

We need to be careful to differentiate from the statement-level abort
behaviour of other RDBMS and the behaviour of SAVEPOINT. It is
theoretically possible to implement them both using nested transactions,
but that doesn't mean they're the same thing.

If a statement has an error, then PostgreSQL currently rolls back the
entire transaction (transaction level abort). It would be good if this
was not the behaviour, since other programs written for other RDBMS do
NOT exhibit this behaviour - this matters a lot if/when statements
error. This behaviour MUST happen implicitly, without additional SQL
statements, otherwise its not the same behaviour. The effect is AS IF
the user had issued the sequence of statements shown above - but they do
not ACTUALLY issue those, hence the reason why the above sequences look
a little wierd.

In the current syntax we're discussing, Oracle's behaviour looks like
this (with all statements in brackets being issued implicitly...) -
using the same example I gave above

BEGIN(SUBBEGIN)    (SUBBEGIN)        INSERT INTO ...    (SUBCOMMIT)    (SUBBEGIN)        INSERT INTO ...    (SUBCOMMIT)
  (SUBBEGIN)        UPDATE ...    (SUBCOMMIT)SUBCOMMIT(SUBBEGIN)    (SUBBEGIN)        INSERT INTO    (SUBCOMMIT)
(SUBBEGIN)       DELETE    (SUBCOMMIT)    (SUBBEGIN)        UPDATE    (SUBCOMMIT)    (SUBBEGIN)        UPDATE
(SUBCOMMIT)SUBCOMMIT
COMMIT (or ROLLBACK)

Note that you CANNOT choose to rollback the statement you're
executing...it just does so if it fails.

As to whether any of that behaviour is strange... That depends upon your
viewpoints and experience, so I could understand that. The situation
remains....it IS the behaviour and my understanding is that this was the
behaviour we were seeking to emulate?

My confusion with the SAVEPOINT/NESTED debate is - how do you know
whether you SHOULD HAVE issued a SUBBEGIN? When I issue a SAVEPOINT, I
don't care whether or not I've issued a SUBBEGIN before, I just do it
and it works.

The only way to do this seems to be to avoid having a flat nesting
structure, but to have an infinite descent on one part of the nesting
tree...so each statement IMPLICTLY starts with a SUBBEGIN, and SAVEPOINT
and COMMIT just count 'em so they know how many SUBCOMMITs to issue to
get back up again.

e.g.
BEGIN
(SUBBEGIN)
INSERT...(SUBBEGIN)INSERT....    (SUBBEGIN)    INSERT...
SAVEPOINT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT)
(SUBBEGIN)
INSERT...(SUBBEGIN)INSERT....    (SUBBEGIN)    INSERT...
COMMIT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT, COMMIT)


Best Regards, Simon Riggs



Re: Nested Transactions, Abort All

From
"Zeugswetter Andreas SB SD"
Date:
> My proposal would be:
>
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
>
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
>
>
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.

Hmm, 1-3 are at least negotiable for the abbreviated form 'BEGIN'
and 'END'. I think we could differentiate those.
The standard only has 'BEGIN TRANSACTION' and 'COMMIT [WORK]'
and 'ROLLBACK [WORK]'. I agree that we are not allowed to change
the semantics of those non abbreviated forms.

How about:
1. Begin main tx: BEGIN WORK | BEGIN TRANSACTION
2. Commit main (all) transaction: COMMIT [ TRANSACTION | WORK ]
3. Rollback main (all) transaction: ROLLBACK [ TRANSACTION | WORK ]

4. BEGIN: starts eighter a main or a subtransaction (for plsql BEGIN SUB)
5. END: commits nested, maybe also abort a nested tx that is already in abort state (for plsql END SUB)
6. ROLLBACK SUB[TRANSACTION]: rolls subtx back

Andreas


Re: Nested Transactions, Abort All

From
"Zeugswetter Andreas SB SD"
Date:
> As far as implementing only savepoints, look at this:
>
> BEGIN;
>     BEGIN;
>     INSERT INTO ...;
>     COMMIT;
>     BEGIN;
>     INSERT INTO ...;
>     COMMIT;
>     BEGIN;
>     INSERT INTO ...;
>     COMMIT;
>
> With savepoints, it looks pretty strange:
>
> BEGIN;
>     SAVEPOINT x1;
>     INSERT INTO ...;
>     SAVEPOINT x2;

If you meant same as your nested example, it would be:

BEGIN TRANSACTION;SAVEPOINT x;INSERT INTO ...;SAVEPOINT x;    -- this implicitly commits previous subtxn xINSERT INTO
...;SAVEPOINTx;INSERT INTO ...; 
COMMIT;

Andreas


Re: Nested Transactions, Abort All

From
Andreas Pflug
Date:
Simon Riggs wrote:

>ISTM - my summary would be
>1. We seem to agree we should support SAVEPOINTs
>
>2. We seem to agree that BEGIN/COMMIT should stay unchanged...
>
>  
>
>>With savepoints, it looks pretty strange:
>>    
>>BEGIN;
>>    SAVEPOINT x1;
>>    INSERT INTO ...;
>>    SAVEPOINT x2;
>>    INSERT INTO ...;
>>    SAVEPOINT x3;
>>    INSERT INTO ...;
>>
>>    
>>
>
>This isn't how you would use SAVEPOINTs...look at this...
>
>BEGIN
>            display one screen to user - book the flight
>    INSERT INTO ...
>    INSERT INTO ...
>    UPDATE ...
>    SAVEPOINT
>            display another related screen - book the hotel
>    INSERT INTO
>    DELETE
>    UPDATE
>    UPDATE
>    SAVEPOINT
>            offer confirmation screen
>COMMIT (or ROLLBACK)
>  
>

No, SAVEPOINT is not some kind of intermediate commit, but a point where 
a rollback can rollback to.
Look at this oracle stuff when googling for SAVEPOINT ROLLBACK:
    BEGIN        SAVEPOINT before_insert_programmers;        insert_programmers (p_deptno);     EXCEPTION        WHEN
OTHERSTHEN ROLLBACK TO before_insert_programmers;     END;
 

There's no need for an intermediate commit, because the top level 
rollback would overrule it (if not, it would be an independent 
transaction, not nested).

I'd opt for BEGIN as a start of a subtransaction (no need for special 
semantics in plpgsql), the corresponding END simply changes the 
transaction context to the parent level.
BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT 
<name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT] <name>. 
For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could be used.
This would be an extension to oracle's usage, which seems quite 
reasonable to me.


Regards,
Andreas




Re: Nested Transactions, Abort All

From
"Zeugswetter Andreas SB SD"
Date:
> I'd opt for BEGIN as a start of a subtransaction (no need for special
> semantics in plpgsql), the corresponding END simply changes the
> transaction context to the parent level.

But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a
statement block. Are we intending to change that ? I think not.

Andreas


Re: Nested Transactions, Abort All

From
Andreas Pflug
Date:
Zeugswetter Andreas SB SD wrote:

>>I'd opt for BEGIN as a start of a subtransaction (no need for special 
>>semantics in plpgsql), the corresponding END simply changes the 
>>transaction context to the parent level.
>>    
>>
>
>But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a 
>statement block. Are we intending to change that ? I think not.
>
>  
>
There are two possibilities:
Either BEGIN *does* start a subtransaction, or BEGIN does not. I don't 
see how two nesting level hierarchies in a function should be 
handleable, i.e. having independent levels of statements blocks and 
subtransactions.

BEGIN [whatever] suggests that there's also a statement closing that 
block of [whatever], but it's very legal for subtransactions to have no 
explicit end; the top level COMMIT does it all.

The SAVEPOINT semantic seems much more appropriate to describe statement 
block independent transactions in this case, so if both is implemented 
for SQL, savepoint only seems enough for plpgsql.

Regards,
Andreas




Re: Nested Transactions, Abort All

From
"Zeugswetter Andreas SB SD"
Date:
> >But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a
> >statement block. Are we intending to change that ? I think not.
> >
> >
> >
> There are two possibilities:
> Either BEGIN *does* start a subtransaction, or BEGIN does not. I don't
> see how two nesting level hierarchies in a function should be
> handleable, i.e. having independent levels of statements blocks and
> subtransactions.
>
> BEGIN [whatever] suggests that there's also a statement closing that
> block of [whatever], but it's very legal for subtransactions to have no
> explicit end; the top level COMMIT does it all.

An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
mean start/end block and subtx. I do not really see a downside.
But, it would imho only make sense if the 'END SUB' would commit sub
or abort sub iff subtx is in aborted state (see my prev posting)

Andreas


Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
Andreas Pflug wrote:

> Simon Riggs wrote:
>
>> ISTM - my summary would be
>> 1. We seem to agree we should support SAVEPOINTs
>>
>> 2. We seem to agree that BEGIN/COMMIT should stay unchanged...
>>
>>  
>>
>>> With savepoints, it looks pretty strange:
>>>     
>>> BEGIN;
>>>     SAVEPOINT x1;
>>>     INSERT INTO ...;
>>>     SAVEPOINT x2;
>>>     INSERT INTO ...;
>>>     SAVEPOINT x3;
>>>     INSERT INTO ...;
>>>
>>>   
>>
>>
>> This isn't how you would use SAVEPOINTs...look at this...
>>
>> BEGIN
>>             display one screen to user - book the flight
>>     INSERT INTO ...
>>     INSERT INTO ...
>>     UPDATE ...
>>     SAVEPOINT
>>             display another related screen - book the hotel
>>     INSERT INTO
>>     DELETE
>>     UPDATE
>>     UPDATE
>>     SAVEPOINT
>>             offer confirmation screen
>> COMMIT (or ROLLBACK)
>>  
>>
>
> No, SAVEPOINT is not some kind of intermediate commit, but a point 
> where a rollback can rollback to.
> Look at this oracle stuff when googling for SAVEPOINT ROLLBACK:
>
>     BEGIN
>         SAVEPOINT before_insert_programmers;
>         insert_programmers (p_deptno);
>      EXCEPTION
>         WHEN OTHERS THEN ROLLBACK TO before_insert_programmers;
>      END;
>
> There's no need for an intermediate commit, because the top level 
> rollback would overrule it (if not, it would be an independent 
> transaction, not nested).
>
> I'd opt for BEGIN as a start of a subtransaction (no need for special 
> semantics in plpgsql), the corresponding END simply changes the 
> transaction context to the parent level.
> BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT 
> <name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT] 
> <name>. For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could 
> be used.
> This would be an extension to oracle's usage, which seems quite 
> reasonable to me.
>
What happens when you use subtransactions?  I think there might be a 
visibility issue and how far do you unwind the depth of subtransactions 
or transactions?

BEGIN UPDATE A SAVEPOINT X BEGIN   BEGIN     UPDATE B     BEGIN       UPDATE C       ROLLBACK TO SAVEPOINT X     COMMIT
 COMMIT COMMIT
 
COMMIT

Or

SAVEPOINT X
BEGIN  UPDATE A  ROLLBACK TO SAVEPOINT X
COMMIT
   


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:

> visibility issue and how far do you unwind the depth of subtransactions 
> or transactions?
> 
> BEGIN
>  UPDATE A
>  SAVEPOINT X
>  BEGIN
>    BEGIN
>      UPDATE B
>      BEGIN
>        UPDATE C
>        ROLLBACK TO SAVEPOINT X

What happens here is that the user will go nuts.  We will have a
prominent entry in the docs: "using both nested transactions and
savepoints inside a transaction can cause confusion.  We recommend you
stick to one or the other."  Or something like that.

(What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
nested transactions created after the SAVEPOINT will be closed.)

So this is another reason why we should use COMMIT to close a nested
transaction: it may refer to a transaction that is already closed
because the user got confused.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)



Re: Nested Transactions, Abort All

From
Pavel Stehule
Date:
> 
> An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
> mean start/end block and subtx. I do not really see a downside.
> But, it would imho only make sense if the 'END SUB' would commit sub
> or abort sub iff subtx is in aborted state (see my prev posting)
> 
> Andreas
> 
Hello,

is good idea use keywords "begin sub" and "end sub"? Programmers like me 
will be an problems with reading and writing SP, because begin sub and 
mostly end sub are keywords from visual basic with different sense. 
BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable

regards
Pavel Stehule



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Pavel Stehule wrote:
> > 
> > An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could
> > mean start/end block and subtx. I do not really see a downside.
> > But, it would imho only make sense if the 'END SUB' would commit sub
> > or abort sub iff subtx is in aborted state (see my prev posting)
> > 
> > Andreas
> > 
> Hello,
> 
> is good idea use keywords "begin sub" and "end sub"? Programmers like me 
> will be an problems with reading and writing SP, because begin sub and 
> mostly end sub are keywords from visual basic with different sense. 
> BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable

I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
TRANSACTION and COMMIT NESTED TRANSACTION.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Bruce Momjian wrote:

> I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
> TRANSACTION and COMMIT NESTED TRANSACTION.

Should I read this as pg will get its own implementation of sub
transactions and not implement the almost equivalent standard (sql99)
savepoint feature?

Will we in the future see savepoints as well? And when that happen, should
we then recommend that people use the standard feature and stay away from
the pg only feature?

Doesn't anyone but me think is all backwards?

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Simon Riggs
Date:
On Fri, 2004-07-09 at 11:45, Andreas Pflug wrote:
> Simon Riggs wrote:
> 
> >ISTM - my summary would be
> >1. We seem to agree we should support SAVEPOINTs
> >
> >2. We seem to agree that BEGIN/COMMIT should stay unchanged...
> >
> >  
> >
> >>With savepoints, it looks pretty strange:
> >>    
> >>BEGIN;
> >>    SAVEPOINT x1;
> >>    INSERT INTO ...;
> >>    SAVEPOINT x2;
> >>    INSERT INTO ...;
> >>    SAVEPOINT x3;
> >>    INSERT INTO ...;
> >>
> >>    
> >>
> >
> >This isn't how you would use SAVEPOINTs...look at this...
> >
> >BEGIN
> >            display one screen to user - book the flight
> >    INSERT INTO ...
> >    INSERT INTO ...
> >    UPDATE ...
> >    SAVEPOINT
> >            display another related screen - book the hotel
> >    INSERT INTO
> >    DELETE
> >    UPDATE
> >    UPDATE
> >    SAVEPOINT
> >            offer confirmation screen
> >COMMIT (or ROLLBACK)
> >  
> >
> 
> No, SAVEPOINT is not some kind of intermediate commit, but a point where 
> a rollback can rollback to.

Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere
you can ROLLBACK to, yes - exactly what I'm saying. 

I've not introduced any concept of "intermediate commit"...

Do you agree that my example is valid Oracle SQL? 

Best Regards, Simon Riggs





Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 07:10:06PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Bruce Momjian wrote:
> 
> > I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
> > TRANSACTION and COMMIT NESTED TRANSACTION.
> 
> Should I read this as pg will get its own implementation of sub
> transactions and not implement the almost equivalent standard (sql99)
> savepoint feature?

I think we should get both.  Clearly savepoints do not allow for a
snapshot to be released; nested xacts do.

OTOH savepoints are trivial to implement once nested xacts are in place.
They are only syntactic sugar.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)



Re: Nested Transactions, Abort All

From
Simon Riggs
Date:
On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:
> 
> > visibility issue and how far do you unwind the depth of subtransactions 
> > or transactions?
> > 
> > BEGIN
> >  UPDATE A
> >  SAVEPOINT X
> >  BEGIN
> >    BEGIN
> >      UPDATE B
> >      BEGIN
> >        UPDATE C
> >        ROLLBACK TO SAVEPOINT X
> 
> What happens here is that the user will go nuts.  We will have a
> prominent entry in the docs: "using both nested transactions and
> savepoints inside a transaction can cause confusion.  We recommend you
> stick to one or the other."  Or something like that.
> 
> (What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
> nested transactions created after the SAVEPOINT will be closed.)
> 
> So this is another reason why we should use COMMIT to close a nested
> transaction: it may refer to a transaction that is already closed
> because the user got confused.

Agreed.

Could we put two modes of operation in?
i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not
allowed to use nested transactions (and vice versa - so they are
mutually exclusive)...

Best Regards, Simon Riggs



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> Clearly savepoints do not allow for a snapshot to be released; nested
> xacts do.

Why not?

> OTOH savepoints are trivial to implement once nested xacts are in place.
> They are only syntactic sugar.

Not only, but simple yes. I'm just opposed to having the non standard
syntax added for the little gain they give over just having standard
savepoints.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> 
> > Clearly savepoints do not allow for a snapshot to be released; nested
> > xacts do.
> 
> Why not?

What is it?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > 
> > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > xacts do.
> > 
> > Why not?
> 
> What is it?

Simon posted it.  It is called RELEASE:

> BEGIN;
>       SAVEPOINT x1;
>       INSERT INTO ...;
>       RELEASE SAVEPOINT x1;
>       SAVEPOINT x1;
>       INSERT INTO ...;
>       RELEASE SAVEPOINT x1;
>       SAVEPOINT x1;
>       INSERT INTO ...;
>       RELEASE SAVEPOINT x1;

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Simon Riggs
Date:
On Fri, 2004-07-09 at 20:34, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > 
> > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > xacts do.
> > > 
> > > Why not?
> > 
> > What is it?
> 
> Simon posted it.  It is called RELEASE:
> 
> > BEGIN;
> >       SAVEPOINT x1;
> >       INSERT INTO ...;
> >       RELEASE SAVEPOINT x1;
> >       SAVEPOINT x1;
> >       INSERT INTO ...;
> >       RELEASE SAVEPOINT x1;
> >       SAVEPOINT x1;
> >       INSERT INTO ...;
> >       RELEASE SAVEPOINT x1;

Yes, this is the DB2 and SQLAnywhere syntax.

Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
Oracle's support of autonomous transactions looks to be identical to
nested transactions (Alvaro's advice required there...). They don't
allow you to explicitly call them, but you can use BEGIN/COMMIT in a
host program that calls a stored procedure, which also contains
BEGIN/COMMIT, effectively giving nested txns.

(...hopefully clearing up any discussion on "intermediate commits"
whoever mentioned those...)

Best regards, Simon Riggs



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > 
> > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > xacts do.
> > > 
> > > Why not?
> > 
> > What is it?
> 
> Simon posted it.  It is called RELEASE:

We can't actually release anything (commit the subtransactions), because
they may be savepoints established after that point, and they are
logically "inside" the previously established ones.  At RELEASE we can't
really release -- we just lose the name and thus the opportunity to
rollback to it.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > > 
> > > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > > xacts do.
> > > > 
> > > > Why not?
> > > 
> > > What is it?
> > 
> > Simon posted it.  It is called RELEASE:
> 
> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones.  At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

Oh, good point.  Yes, those savepoints in between are still active.  But
do we release anything on subxact commit?  I though it was only on xact
abort, and that does invalidate all the savepoints in between.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Fri, 2004-07-09 at 20:34, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > > 
> > > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > > xacts do.
> > > > 
> > > > Why not?
> > > 
> > > What is it?
> > 
> > Simon posted it.  It is called RELEASE:
> > 
> > > BEGIN;
> > >       SAVEPOINT x1;
> > >       INSERT INTO ...;
> > >       RELEASE SAVEPOINT x1;
> > >       SAVEPOINT x1;
> > >       INSERT INTO ...;
> > >       RELEASE SAVEPOINT x1;
> > >       SAVEPOINT x1;
> > >       INSERT INTO ...;
> > >       RELEASE SAVEPOINT x1;
> 
> Yes, this is the DB2 and SQLAnywhere syntax.
> 
> Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.

I thouht ROLLBACK was different from RELEASE, no?  I see ROLLBACK used
in SQL99 for savepoints:
       ROLLBACK [ WORK ] [ AND [ NO ]  CHAIN ]               [ <savepoint clause> ]


RELEASE only discards the savepoint name, I thought.

> Oracle's support of autonomous transactions looks to be identical to
> nested transactions (Alvaro's advice required there...). They don't
> allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> host program that calls a stored procedure, which also contains
> BEGIN/COMMIT, effectively giving nested txns.

Oracle has nested transactions too?  Can you supply an example?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Simon Riggs
Date:
On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote:
> Simon Riggs wrote:
> > 
> > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
> 
> I thouht ROLLBACK was different from RELEASE, no?  I see ROLLBACK used
> in SQL99 for savepoints:
> 
>         ROLLBACK [ WORK ] [ AND [ NO ]  CHAIN ]
>                 [ <savepoint clause> ]
> 
> 
> RELEASE only discards the savepoint name, I thought.
> 

ERR-OHH Yes, dead right. Forgive my confusion, I knew there was
something different about Oracle's support of RELEASE. Check out:

https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm

last thing on page...

> > Oracle's support of autonomous transactions looks to be identical to
> > nested transactions (Alvaro's advice required there...). They don't
> > allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> > host program that calls a stored procedure, which also contains
> > BEGIN/COMMIT, effectively giving nested txns.
> 
> Oracle has nested transactions too?  Can you supply an example?

It's hard to quote a short example.... so try this link instead

http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm

Wasn't there some description of autonomous transactions on the stuff I
sent previously?

Best Regards, Simon Riggs



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote:
> > Simon Riggs wrote:
> > > 
> > > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
> > 
> > I thouht ROLLBACK was different from RELEASE, no?  I see ROLLBACK used
> > in SQL99 for savepoints:
> > 
> >         ROLLBACK [ WORK ] [ AND [ NO ]  CHAIN ]
> >                 [ <savepoint clause> ]
> > 
> > 
> > RELEASE only discards the savepoint name, I thought.
> > 
> 
> ERR-OHH Yes, dead right. Forgive my confusion, I knew there was
> something different about Oracle's support of RELEASE. Check out:
> 
> https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm
> 
> last thing on page...

Interesting Oracle doesn't support RELEASE or savepoint levels:

T271, Savepoints     Oracle supports this feature, except:
   * Oracle does not support RELEASE SAVEPOINT.   * Oracle does not support savepoint levels.

> > > Oracle's support of autonomous transactions looks to be identical to
> > > nested transactions (Alvaro's advice required there...). They don't
> > > allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> > > host program that calls a stored procedure, which also contains
> > > BEGIN/COMMIT, effectively giving nested txns.
> > 
> > Oracle has nested transactions too?  Can you supply an example?
> 
> It's hard to quote a short example.... so try this link instead
> 
> http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm
> 
> Wasn't there some description of autonomous transactions on the stuff I
> sent previously?

This is not a nested transaction.  It is "autonomous" meaning it can
commit independent of the outer transaction:
The grand new benefit of autonomous transactions for database triggersis that inside those triggers you can now issue
COMMITsand ROLLBACKs,statements that are otherwise not allowed in database triggers. Thechanges you commit and roll
backwill not, however, affect the maintransaction that caused the database trigger to fire. They will onlyapply to DML
activitytaking place inside the trigger itself (or throughstored program units called within the trigger).
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> > Simon posted it.  It is called RELEASE:
> 
> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones.  At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

You can still perform the release. If we have
 SAVEPOINT p1;
 SAVEPOINT p2;
 RELEASE p2;

then it's no problem, we released the topmost savepoint (commit the
corresponding subtransaction). And if we have
 SAVEPOINT p1;
 SAVEPOINT p2;
 RELEASE p1;

now you are saying that we just forget the name p1 and the subtransaction
for p1 is still there for ever.

But one should also link the subtransaction for p1 to p2, so when p2 is
released then also the (now unnamed) subtransaction for p1 is commited. Of
course we can't release p1 as long as p2 is still active. p2 is logically
a subtransaction of p1.

One don't really need an explicit link. When p2 is released all one needs 
to do is to look at the parent and see if that is still named, if not it 
should be commited and so on until we reach a named one.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 04:07:19PM -0400, Bruce Momjian wrote:
> Alvaro Herrera wrote:

> > We can't actually release anything (commit the subtransactions), because
> > they may be savepoints established after that point, and they are
> > logically "inside" the previously established ones.  At RELEASE we can't
> > really release -- we just lose the name and thus the opportunity to
> > rollback to it.
> 
> Oh, good point.  Yes, those savepoints in between are still active.  But
> do we release anything on subxact commit?  I though it was only on xact
> abort, and that does invalidate all the savepoints in between.

Yes, we free some things.  Granted it's not a lot, but we have stacks
for several things that will be always be growing with savepoints, but
we can chop their heads off with "commit nested."

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jason Tesser: You might not have understood me or I am not understanding you.
Paul Thomas: It feels like we're 2 people divided by a common language...



Re: Nested Transactions, Abort All

From
Thomas Swan
Date:
Alvaro Herrera wrote:

>On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:
>
>  
>
>>visibility issue and how far do you unwind the depth of subtransactions 
>>or transactions?
>>
>>BEGIN
>> UPDATE A
>> SAVEPOINT X
>> BEGIN
>>   BEGIN
>>     UPDATE B
>>     BEGIN
>>       UPDATE C
>>       ROLLBACK TO SAVEPOINT X
>>    
>>
>
>What happens here is that the user will go nuts.  We will have a
>prominent entry in the docs: "using both nested transactions and
>savepoints inside a transaction can cause confusion.  We recommend you
>stick to one or the other."  Or something like that.
>
>(What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
>nested transactions created after the SAVEPOINT will be closed.)
>
>So this is another reason why we should use COMMIT to close a nested
>transaction: it may refer to a transaction that is already closed
>because the user got confused.
>
>  
>
Technically, a ROLLBACK TO SAVE POINT X would be an ABORT on all nested 
transactions.  COMMIT means that if the parent transaction commits then 
the child transaction will also commit.

BEGIN BEGIN   UPDATE A ROLLBACK UPDATE B
COMMIT

The changes from UPDATE A will not commit with the changes from UPDATE B.

BEGIN BEGIN   UPDATE A COMMIT UPDATE B
COMMIT

The changes from UPDATE A will commit with the changes from UPDATE B.

BEGIN BEGIN   UPDATE A COMMIT UPDATE B
ROLLBACK

The changes from UPDATE A will not commit with the changes from UPDATE 
B, and the changes from UPDATE B will not commit either.



Re: Nested Transactions, Abort All

From
"Min Xu (Hsu)"
Date:
Dear all,

I've being following the discussion of the nested transaction. I 
apologize for that I can't help asking my questions as I get more 
confused about what exactly are nested transactions, at least as far as 
the concurrency control goes.

It seems to me there are two different types of nested transactions, 
both to improve the parallelism to a transaction, but they have 
different semantics.

The first type of nested transactions, I believe as described in this paper:

http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal

has the semantics that the inner (or children) transactions are totally 
hidden within a outer (or parent) transaction. Concurrency control makes 
sure not only the entire (including children) parent transaction is 
serial with other (parent) transaction, but also all child transactions 
are serial inside the parent transaction. Clearly, this speedup the 
execution of the parent transaction when child transactions are executed 
in parallel. I think this semantics is also documented here:

http://pybsddb.sourceforge.net/ref/transapp/nested.html

On the other hand, I believe another semantics of nested transactions is 
that to allow child transactions to commit independently to the parent 
transaction. The logger example in this link given by a previous  post 
in this discussion

http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm

is a good example on this semantics. As far as the concurrency control 
goes, the parent transaction and the children transactions are treated 
equally. I.e. if after a child transaction is finished and before its 
parent transaction commits, a conflict with the child transaction will 
not cause the parent transaction to rollback. Again, this allows more 
parallelism to the the parent transaction.

Am I on the right track understanding the serializability semantics 
here? I'd appreciate it if someone can direct me some authorative text 
on these issues.

Thanks,

-Min



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> Yes, we free some things.  Granted it's not a lot, but we have stacks
> for several things that will be always be growing with savepoints,

They will not always be growing for savepoints, you can free things when 
using savepoints just as with subtransactions.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Alvaro Herrera wrote:

> We can't actually release anything (commit the subtransactions), because
> they may be savepoints established after that point, and they are
> logically "inside" the previously established ones.  At RELEASE we can't
> really release -- we just lose the name and thus the opportunity to
> rollback to it.

The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints 
subsequent to the RELEASE:

1) Let S be the <savepoint name>.
2) If S does not identify a savepoint established in the current 
savepoint level,then an exception condition is raised:savepoint 
exception  invalid specification.
3) The savepoint identified by S and all savepoints established in the 
current savepoint level subsequent to the establishment of S are destroyed.

So it sounds like we can commit the subtransaction on RELEASE.

Note that this is *not* the same when a savepoint name is reused; that 
just moves the name, but "contained" savepoints are still valid.

-O


Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Oliver Jowett wrote:

> The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints 
> subsequent to the RELEASE:

"subsequent to the released savepoint" rather.

-O


Re: Nested Transactions, Abort All

From
Mike Rylander
Date:
Dennis Bjorklund wrote:

> On Fri, 9 Jul 2004, Bruce Momjian wrote:
> 
>> I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED
>> TRANSACTION and COMMIT NESTED TRANSACTION.
> 
> Should I read this as pg will get its own implementation of sub
> transactions and not implement the almost equivalent standard (sql99)
> savepoint feature?
> 
> Will we in the future see savepoints as well?

I'm not a core developer, but that is what it looks like.

> And when that happen, should 
> we then recommend that people use the standard feature and stay away from
> the pg only feature?

Nested transactions and savepoints serve two different purposes.  They have
some overlap, but for the most part solve two distinct problems.

> 
> Doesn't anyone but me think is all backwards?
> 

I don't think so, especially as there has been some talk of implimenting
savepoints as a subset of nested transactions.

--miker



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Min Xu (Hsu) wrote:
> Dear all,
> 
> I've being following the discussion of the nested transaction. I 
> apologize for that I can't help asking my questions as I get more 
> confused about what exactly are nested transactions, at least as far as 
> the concurrency control goes.
> 
> It seems to me there are two different types of nested transactions, 
> both to improve the parallelism to a transaction, but they have 
> different semantics.
> 
> The first type of nested transactions, I believe as described in this paper:
> 
> http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal
> 
> has the semantics that the inner (or children) transactions are totally 
> hidden within a outer (or parent) transaction. Concurrency control makes 
> sure not only the entire (including children) parent transaction is 
> serial with other (parent) transaction, but also all child transactions 
> are serial inside the parent transaction. Clearly, this speedup the 
> execution of the parent transaction when child transactions are executed 
> in parallel. I think this semantics is also documented here:
> 
> http://pybsddb.sourceforge.net/ref/transapp/nested.html
> 
> On the other hand, I believe another semantics of nested transactions is 
> that to allow child transactions to commit independently to the parent 
> transaction. The logger example in this link given by a previous  post 
> in this discussion
> 
> http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm
> 
> is a good example on this semantics. As far as the concurrency control 
> goes, the parent transaction and the children transactions are treated 
> equally. I.e. if after a child transaction is finished and before its 
> parent transaction commits, a conflict with the child transaction will 
> not cause the parent transaction to rollback. Again, this allows more 
> parallelism to the the parent transaction.
> 
> Am I on the right track understanding the serializability semantics 
> here? I'd appreciate it if someone can direct me some authorative text 
> on these issues.

You are actually talking about much more powerful nested transactions
than we have implemented currently.  The first allows for parallel
execution, which is certainly interesting.  The second allows
subtransactions to be committed/rolled back independent of the outer
transaction.  We don't support that either.

Our current implementation merely allows parts of a transaction to be
rolled back using ROLLBACK NESTED.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote:
> Alvaro Herrera wrote:
> 
> >We can't actually release anything (commit the subtransactions), because
> >they may be savepoints established after that point, and they are
> >logically "inside" the previously established ones.  At RELEASE we can't
> >really release -- we just lose the name and thus the opportunity to
> >rollback to it.
> 
> The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints 
> subsequent to the RELEASE:

In our case, invalidating a savepoint does not mean we can release its
resources.  We can only do that if it's the latest defined savepoint.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote:
> On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> 
> > Yes, we free some things.  Granted it's not a lot, but we have stacks
> > for several things that will be always be growing with savepoints,
> 
> They will not always be growing for savepoints, you can free things when 
> using savepoints just as with subtransactions.

I still don't see when I can release a savepoint's state.

You showed a particular case, where we can finish a released savepoint
that is the innermost transaction.  However, as soon as there is another
savepoint set after the released savepoint was set, we can't free the
second.

I mean this:

begin; ... work ...; savepoint foo; ... more work ...; savepoint bar; ... yet more ... ; release foo;


At this time I can't release savepoint foo because the implementation
(nested) requires me to keep it open as long as savepoint bar exists.
If I released bar at a later time, I could close both, but not before.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Qué importan los años?  Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo"  (Mafalda)



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> I mean this:
> 
> begin;
>   ... work ...;
>   savepoint foo;
>   ... more work ...;
>   savepoint bar;
>   ... yet more ... ;
>   release foo;
> 
> 
> At this time I can't release savepoint foo because the implementation
> (nested) requires me to keep it open as long as savepoint bar exists.
> If I released bar at a later time, I could close both, but not before.

Yes, and that is exactly what should be done, what is wrong with that 
behaviour?

If you do the same as above with nested transactions

BEGIN;  ... work ...;  SUBBEGIN;  ... more work ...;  SUBBEGIN;  ... yet more ... ;  
and now you can only commit the last subbegin. Subtransactions does not 
give you anything more then savepoints in this example.

If anything there might be a possibility to do more with savepoints then 
nested transactions since as you say, you can release an earlier savepoint 
then the last. But that is something one can try to optimize later, if 
possible to optimize at all.

Subtransactions can _not_ free more things then savepoints can. It's just
an empty argument.

In fact, I still see no real advantage to subtransactions at all. We are
only playing the lock-in game when we introduce postgresql features that
do almost the same thing as standard features. 

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Fri, 9 Jul 2004, Mike Rylander wrote:

> Nested transactions and savepoints serve two different purposes.  They have
> some overlap, but for the most part solve two distinct problems.

Then show some examples that illustrait the difference. So far all 
examples shown that uses subtransactions could just as well have been 
written using savepoints.

I don't agree that they have two different purposes.

> I don't think so, especially as there has been some talk of implimenting
> savepoints as a subset of nested transactions.

It is not a subset. It's the other way around. Nested transactions are a
subset of savepoints

Savepoints have more possibilities, you can invalidate older savepoints 
then the last (with subtransactions you can only commit/rollback the 
last). If you don't use that then it's exactly the same as 
subtransactions.

The only "feature" subtransactions have that savepoints doesn't is the
lack of names. Every savepoint have a name. If we want an extension it 
could be to get the database to generate a fresh savepoint name. The 
client can of course also generate unique savepoint names if it want.

That subtransactions do more than savepoints is just smoke an mirrors. So
far there have been no example to validate that point of view, and I don't
think there will be any. If anyone know of something that you can do with
subtransactions and not with savepoints, please speak up.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Gavin Sherry
Date:
On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote:
> > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> >
> > > Yes, we free some things.  Granted it's not a lot, but we have stacks
> > > for several things that will be always be growing with savepoints,
> >
> > They will not always be growing for savepoints, you can free things when
> > using savepoints just as with subtransactions.
>
> I still don't see when I can release a savepoint's state.
>
> You showed a particular case, where we can finish a released savepoint
> that is the innermost transaction.  However, as soon as there is another
> savepoint set after the released savepoint was set, we can't free the
> second.
>
> I mean this:
>
> begin;
>   ... work ...;
>   savepoint foo;
>   ... more work ...;
>   savepoint bar;
>   ... yet more ... ;
>   release foo;
>
>
> At this time I can't release savepoint foo because the implementation
> (nested) requires me to keep it open as long as savepoint bar exists.
> If I released bar at a later time, I could close both, but not before.

According to ANSI 2003, savepoints should be considered in terms of
nesting. That is, the spec talks to nesting levels (4.35.2):

"An SQL-transaction has one or more savepoint levels, exactly one of which
is the current savepoint level. The savepoint levels of an SQL-transaction
are nested, such that when a new savepoint level NSL is established, the
current savepoint level CSL ceases to be current and NSL becomes current.
When NSL is destroyed, CSL becomes current again."

And:

"If a <rollback statement> references a savepoint SS, then all changes
made to SQL-data or schema subsequent to the establishment of the
savepoint are canceled, all savepoints established since SS was
established are destroyed, and the SQL-transaction is restored to its
state as it was immediately following the execution of the <savepoint
statement>."

This is also relevant:

"It is implementation-defined whether or not, or how, a <rollback
statement> that references a <savepoint specifier> affects diagnostics
area contents, the contents of SQL descriptor areas, and the status of
prepared statements."

So, releasing foo would release bar (16.5):

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

Also, the spec makes mention of savepoint behaviour in functions (10.4):

"2) If, before the completion of the execution of the SQL routine body of
R, an attempt is made to execute an SQL-transaction statement that is not
a <savepoint statement> or a <release savepoint statement>, or is a <rollback
statement> that does not specify a <savepoint clause>, then an exception
condition is raised: SQL routine exception   prohibited SQL-statement
attempted. "

It also states that an SQL-invoked function lives in its own savepoint
level (4.27):

"An SQL-invoked procedure may optionally be specified to require a new
savepoint level to be established when it is invoked and destroyed on
return from the executed routine body. The alternative of not taking a
savepoint can also be directly specified with OLD SAVEPOINT LEVEL. When an
SQL-invoked function is invoked a new savepoint level is always
established."

We do not currently support SQL-invoked procedures (that is, routines
executed from SQL with CALL <procname>, which don't need to return a value
and which can accept IN OUT and OUT parameters) so we need only deal with
the SQL-invoked function case.

So, running back to 10.4:

"12) If R is an SQL-invoked function or if R is an SQL-invoked procedure
and the descriptor of R includes an indication that a new savepoint level
is to be established when R is invoked, then the current savepoint level
is destroyed."

So, any savepoints created during the function are destroyed.

What isn't clearly discussed is what they mean by destroy. That is, the
1) ability to reference the savepoint, or 2) all modifications to SQL-data
made since the savepoint was created. I cannot see how it could be (2) can
be the case.

Section 16.5 discusses <release savepoint statement> whose function is to
'destroy a savepoint':

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

It makes no reference to have any effect like rollback.

So, I think that we can only release things once we rollback to a
savepoint or once we commit.

This is not to say we should follow this implementation. I've dug this up
to try and present one (reasonably) consistent perspective on it.

Thanks,

Gavin


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Gavin Sherry wrote:

> "3) The savepoint identified by S and all savepoints established in the
> current savepoint level subsequent to the establishment of S are
> destroyed."

So the standard savepoints are even more like the subtransactions that
alvaro have implemented then I realised before.

One can not just release an earlier savepoint and keep a later one. 
Interesting.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Andreas Pflug
Date:
Simon Riggs wrote:

>>>BEGIN
>>>            display one screen to user - book the flight
>>>    INSERT INTO ...
>>>    INSERT INTO ...
>>>    UPDATE ...
>>>    SAVEPOINT
>>>            display another related screen - book the hotel
>>>    INSERT INTO
>>>    DELETE
>>>    UPDATE
>>>    UPDATE
>>>    SAVEPOINT
>>>            offer confirmation screen
>>>COMMIT (or ROLLBACK)
>>> 
>>>
>>>      
>>>
>>No, SAVEPOINT is not some kind of intermediate commit, but a point where 
>>a rollback can rollback to.
>>    
>>
>
>Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere
>you can ROLLBACK to, yes - exactly what I'm saying. 
>
>I've not introduced any concept of "intermediate commit"...
>
>Do you agree that my example is valid Oracle SQL? 
>  
>
If you name that SAVEPOINT statements, yes. But the ordering of 
statements makes the second savepoint useless, because it's after all 
datamodifying statements; the first three are not covered by a savepoint 
at all, only the toplevel xaction. That's probably not what you wanted.

Regards,
Andreas


Re: Nested Transactions, Abort All

From
Andreas Pflug
Date:
Bruce Momjian wrote:

>
>Interesting Oracle doesn't support RELEASE or savepoint levels:
>
>T271, Savepoints     Oracle supports this feature, except:
>
>    * Oracle does not support RELEASE SAVEPOINT.
>  
>
Yes, and IMHO it's just some housekeeping stuff, informing the backend 
that you'll never want to rollback to that savepoint any more. If there 
are no resources to release internally, a noop.

>    * Oracle does not support savepoint levels.
>  
>
The levels are created implicitely, because a rollback to savepoint1 
will rollback *all* work done since then. This effectively makes all 
subsequent savepoints children of the first. The effect of RELEASE 
SAVEPOINT would be to restrict the tree depth, by concentrating all 
released savepoints into their parents.


>
>This is not a nested transaction.  It is "autonomous" meaning it can
>commit independent of the outer transaction:
>

I like that too... in 7.6.

Regards,
Andreas



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Alvaro Herrera wrote:
> On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote:
> 
>>Alvaro Herrera wrote:
>>
>>
>>>We can't actually release anything (commit the subtransactions), because
>>>they may be savepoints established after that point, and they are
>>>logically "inside" the previously established ones.  At RELEASE we can't
>>>really release -- we just lose the name and thus the opportunity to
>>>rollback to it.
>>
>>The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints 
>>subsequent to the RELEASE:

(that should read "subsequent to the released savepoint")

> In our case, invalidating a savepoint does not mean we can release its
> resources.  We can only do that if it's the latest defined savepoint.

I don't understand why this is true if the invalidation comes from a 
RELEASE statement. I understand the problems with savepoint name reuse 
invalidating an earlier savepoint -- we do have to keep the earlier txn 
open in that case.

Say I have:
  SAVEPOINT s1  -- work 1  SAVEPOINT s2  -- work 2  RELEASE SAVEPOINT s1    -- Invalidates s1 and s2

Can't we translate that to:
  begin subtransaction s1    -- work 1    begin subtransaction s2      -- work 2  commit subtransaction s1   -- and
implicitlys2
 

We don't need to keep subtransaction s2 open -- we will never need to 
roll it back as the RELEASE of s1 invalidates it.

What am I missing?

-O


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Mike Rylander wrote:

> They do, if only to make particular constructs easier to write.  This is an
> opinion, but for example an EXCEPTION framework for plpgsql would be easier
> to implement and use if it used the nested transactions rather than
> savepoint syntax:
> 
> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
> BEGIN
>         BEGIN NESTED;
>                 do some work...
>                 BEGIN NESTED;
>                         do other work...
>                 EXCEPTION WHEN SQLSTATE = already_exists THEN
>                         do alternate work with its own error checking...
>                 END NESTED;
>         EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>                 ROLLBACK NESTED;
>         END NESTED;
> END;';
> 
> I realize this can be done with nested savepoints and that is what the spec
> requires,

Lets look at what it can look like:

BEGIN         SAVEPOINT nested;                 do some work...                 SAVEPOINT nested2;
  do other work...                 EXCEPTION WHEN SQLSTATE = already_exists THEN                         ROLLBACK TO
SAVEPOINTnested2;                         do alternate work with its own error checking...                 RELEASE
nested2;        EXCEPTION WHEN SQLSTATE = fkey_violation THEN                 ROLLBACK TO SAVEPOINT nested;
RELEASEnested;
 
END;


Now, in what way is this more complicated?

I'm not 100% sure how the exceptions that you used above work. Do that
always rollback the transaction thay are in? In one of the exceptions you
did a rollback but not in the other. In my example I added a rollback in
the first exception handler. Maybe you forgot it there?

In any case. I don't see this as any harder then your example.

> > Savepoints have more possibilities, you can invalidate older savepoints
> > then the last (with subtransactions you can only commit/rollback the
> > last).
> 
> This implies that savepoints are flat.  It won't be that way under the
> covers, but it does give that impression, and flat savepoint space is
> definitely suited to a different class of problems than nested
> transactions.

First, my claim above was wrong. As Gavin pointed out in another mail, if
one have savepoints p1 and p2 and release p1 then also p2 is released.
It's possible to implement both kinds of behaviour using Alvaros work, but
the standard demands the simpler one where p2 is also released.

Now, about the flatness. Savepoints are not flat. They are sort of flat in
a savepoint level. But, for example when you call a function you get a new
savepoint level. I actually don't want to call it flat at all. The example
above does not overwrite the savepoints "nested" and "nested2" that might
exist before the call, since this is a higher savepoint level.

I'm not sure exactly what it is that defines a new savepoint level, but a 
function call does and maybe some other things.

> BTW, I would imagine that savepoints will be implemented as nested
> transactions with detachable labels... the label can move from a
> transaction to one of its descendants, and that outer (sub)transaction will
> be implicitly COMMITed with its parent.

Yes. That's my view as well.

> Alvaro found it easier to implement nested transactions, he forged ahead and
> did it.  Now, because of good design or simple luck, we should be able to
> implement savepoints fairly easily.

I think the difference between them are so small that it's not a big deal
at all. In my view savepoints and nested transactions are almost the same
thing. The only difference being that the savepoints have names.  
Savepoints are nested. You can not have savepoints p1 and then p2 and try
to only rollback p1. Then you rollback p2 as well, why. Because they are
nested.

> spec WRT savepoints, we actually get to present a richer interface to the
> user

If it's richer or not is the question. And then one have to compare that 
to the downside of adding a non standard interface.

I don't think it is richer at all, but I'd be happy to change my mind if
someone can show an example where nested transactions solve something that
you can't just as well solve with savepoints.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Tom Lane
Date:
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Fri, 9 Jul 2004, Mike Rylander wrote:
>> Nested transactions and savepoints serve two different purposes.  They have
>> some overlap, but for the most part solve two distinct problems.

> Then show some examples that illustrait the difference. So far all 
> examples shown that uses subtransactions could just as well have been 
> written using savepoints.

And vice versa.  It's a matter of convenience of notation, and I tend
to agree with Mike's comment that each wins in some cases.

> Savepoints have more possibilities, you can invalidate older savepoints 
> then the last 

Nonsense.  Invalidating an older savepoint must invalidate everything
after it as well.  The fact that the savepoint syntax allows you to
express conceptually-ridiculous operations (like that one) is not a
point in its favor IMHO.
        regards, tom lane


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Tom Lane wrote:

> Nonsense.  Invalidating an older savepoint must invalidate everything
> after it as well.  The fact that the savepoint syntax allows you to
> express conceptually-ridiculous operations (like that one) is not a
> point in its favor IMHO.

Luckily the standard was written like that!

On the other hand, it's not hard to implement the other behaviour either
if that is what one wants (and we don't). It would only forget the name of
the earlier savepoint. The corresponding transaction in itself have to
stay.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sat, Jul 10, 2004 at 06:22:00PM +0200, Dennis Bjorklund wrote:

> On the other hand, it's not hard to implement the other behaviour either
> if that is what one wants (and we don't). It would only forget the name of
> the earlier savepoint. The corresponding transaction in itself have to
> stay.

That's why it's absurd.  Why allow an operation which isn't really an
operation?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"God is real, unless declared as int"



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Fri, Jul 09, 2004 at 08:03:36PM +0100, Simon Riggs wrote:
> On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote:

> > So this is another reason why we should use COMMIT to close a nested
> > transaction: it may refer to a transaction that is already closed
> > because the user got confused.

Sorry!  I wanted to say that we SHOULDN'T use "commit" to close a nested
transaction.  Rather we want to use a different command just so the
confusion does not close the outer transaction, which would not be what
the user wanted to do.

> Could we put two modes of operation in?
> i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not
> allowed to use nested transactions (and vice versa - so they are
> mutually exclusive)...

This may be a good idea.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Always assume the user will do much worse than the stupidest thing
you can imagine."                                (Julien PUYDT)



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
People,

Are we perhaps getting away from the issues here?   The reason for this 
discussion was to determine the user-level syntax for Alvaro's nested 
transactions.   We can discuss all we want about how he should have maybe 
implemented some things differently, but we're supposed to start beta-testing 
in 5 days and the current tangentalism of this discussion is unlikely to 
produce such a result.

What we really have to determine is one of 4 options regarding the syntax for 
Alvaro's patch:

1) We adopt one of the two PostgreSQL-specific syntaxes suggested by Alvaro, 
based on SUBBEGIN or BEGIN NESTED.   This would probably be the easiest 
solution, but adds inconsistency with both the standard and other databases.

2) We adopt the syntax of the other databases to really push Nested 
Transactions (as opposed to Savepoints), namely MSSQL and SyBase.  This would 
aid thousands of DBAs wishing to migrate to PostgreSQL, but would also mean 
adopting a logically inconsistent syntax which is even further from the 
standard than Alvaro's proposal.

3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax.   This would 
have the twin benefit of both allowing us to improve our standards compliance 
and make savepoints completely compliant in the next version, as well as 
helping those wishing to migrate from Oracle to PostgreSQL (currently our 
largest source of migrations).  Its disadvantage is the subtle differences 
between Alvaro's patch and the standard, which might not be obvious to users 
and lead to difficult to locate errors.   This option also comes in two 
flavors:a) we implement savepoint names, troubleshooting the namespace and scoping 
issues, which would really make this a different feature and delay beta 
testing, orb) we do anonymous savepoints for now, which more-or-less exactly matches the 
current behavior of Alvaro's patch, and do complaint, named savepoints in the 
next version.
4) We hold back this patch until the next version.   There is some merit in 
this, due to the lack of consensus on functionality and Alvaro's 
dissapointing discovery that we will not be able to use savepoints in 
functions until next version.   However, it would also mean effectively 
dropping a major feature from 7.5 pretty much because we can't make up our 
minds, and because nobody gave Alvaro adequate feedback when it was more 
timely.

If you couldn't tell, I favor option 3) b)    This syntax would look like:

BEGIN TRANSACTION;  --begin maindo stuff;SAVEPOINT; -- begin "nested transaction 1"    do more stuff;    SAVEPOINT; --
begin"nested transaction 2" inside "NT 1"        do stuff;    RELEASE SAVEPOINT; -- "commit" NT 2    do some more
stuff;   test conditions: if bad:        ROLLBACK TO SAVEPOINT;  -- rollback NT1, erasing NT2 in the process    if
good:       RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2do some more stufftests: if problem:    ROLLBACK;
--rollback entire transaction, including NT1 and NT2;if good:    COMMIT; -- commit entire transaction, including NT1
and/orNT2             if they were good, excluding them if they were rolled back
 

In other words:SAVEPOINT == BEGIN NESTEDRELEASE SAVEPOINT == COMMIT NESTEDROLLBACK TO SAVEPOINT == ROLLBACK NESTED

If I'm not mistaken, the above matches the functionality already coded by 
Alvaro.   It begins but does not complete our compliance with SQL3 Savepoint 
syntax, putting us on the right road but making developers aware that there 
are some differences between our implementation and the standard.  Thus 
developers would be able to adopt the current syntax now, and the same 
applications would still run when we complete standards-compliant syntax 
later.

HOWEVER, I do still find one major flaw in Alvaro's implementation that I 
can't seem to get other people on this list to take seriously, or maybe I'm 
just not understanding the answers.  One-half the point of Savepoints/Nested 
Transactions is the ability to recover from certain kinds of errors (like 
duplicate keys) inside a transaction and still commit the transaction after 
the abort condition has been rolled back.  But the ability to detect an abort state *from the SQL command line* (or a 
database port connection) has not been addressed.   I've seen some comments 
about functions to find an abort state from libpq in the text, but I'm not 
even clear if this has been coded or is just theoretical.   Parsing the 
output of STDERR is *not* adequate.   We need to be able to query whether we 
are in an abort state, or we make NTs absolutely useless to any client 
application that has connections which cannot, or do not yet, incorporate new 
libpq functions, something which could take considerable time after the 7.5 
release.Do we already have an ability to query the SQLSTATE from the command line?  
If so, what numbers indicate an abort state, if any?   Without this issue being addressed, I will change my opinion and
votefor 
 
option (4) because clearly the NT patch will not be ready for prime-time.    
-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Alvaro Herrera wrote:

> That's why it's absurd.  Why allow an operation which isn't really an
> operation?

Same reason why you allow an addition with 0. One can say that it's 
not really an operation either.

One can have many different semantics, here are 3 versions:
1) You release savepoints in any order2) You release savepoints in reverse order3) You release any savepoints and later
onesthen the   one you released are automatically released.
 

I don't see any of these as absurd. The ansi spec uses number 3.

It might seem absurd to you, given the implementation you have made.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sat, Jul 10, 2004 at 09:46:00PM +0200, Dennis Bjorklund wrote:

> One can have many different semantics, here are 3 versions:
> 
>  1) You release savepoints in any order
>  2) You release savepoints in reverse order
>  3) You release any savepoints and later ones then the
>     one you released are automatically released.
> 
> I don't see any of these as absurd. The ansi spec uses number 3.

Ah-ha, now I see what I failed to see previously: all later savepoints
are also released!  This makes a lot more sense.  So "release" is
exactly like commit nested, allowing several levels to be "committed".

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Josh Berkus wrote:
> If you couldn't tell, I favor option 3) b)    This syntax would look like:
> 
> BEGIN TRANSACTION;  --begin main
>     do stuff;
>     SAVEPOINT; -- begin "nested transaction 1"
>         do more stuff;
>         SAVEPOINT; -- begin "nested transaction 2" inside "NT 1"
>             do stuff;
>         RELEASE SAVEPOINT; -- "commit" NT 2
>         do some more stuff;
>         test conditions: if bad:
>             ROLLBACK TO SAVEPOINT;  -- rollback NT1, erasing NT2 in the process
>         if good:
>             RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2
>     do some more stuff
>     tests: if problem:
>         ROLLBACK; -- rollback entire transaction, including NT1 and NT2;
>     if good:
>         COMMIT; -- commit entire transaction, including NT1 and/or NT2
>                  if they were good, excluding them if they were rolled back

Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
perhaps not allowing easy migration from Oracle.

> In other words:
>     SAVEPOINT == BEGIN NESTED
>     RELEASE SAVEPOINT == COMMIT NESTED
>     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> 
> If I'm not mistaken, the above matches the functionality already coded by 
> Alvaro.   It begins but does not complete our compliance with SQL3 Savepoint 
> syntax, putting us on the right road but making developers aware that there 
> are some differences between our implementation and the standard.  Thus 
> developers would be able to adopt the current syntax now, and the same 
> applications would still run when we complete standards-compliant syntax 
> later.
> 
> HOWEVER, I do still find one major flaw in Alvaro's implementation that I 
> can't seem to get other people on this list to take seriously, or maybe I'm 
> just not understanding the answers.  One-half the point of Savepoints/Nested 
> Transactions is the ability to recover from certain kinds of errors (like 
> duplicate keys) inside a transaction and still commit the transaction after 
> the abort condition has been rolled back.  
>     But the ability to detect an abort state *from the SQL command line* (or a 
> database port connection) has not been addressed.   I've seen some comments 
> about functions to find an abort state from libpq in the text, but I'm not 
> even clear if this has been coded or is just theoretical.   Parsing the 
> output of STDERR is *not* adequate.   We need to be able to query whether we 
> are in an abort state, or we make NTs absolutely useless to any client 
> application that has connections which cannot, or do not yet, incorporate new 
> libpq functions, something which could take considerable time after the 7.5 
> release.
>     Do we already have an ability to query the SQLSTATE from the command line?  
> If so, what numbers indicate an abort state, if any?   
>     Without this issue being addressed, I will change my opinion and vote for 
> option (4) because clearly the NT patch will not be ready for prime-time.

Don't we see the error from libpq PQexec() return value and other
interfaces?  Are you saying how do we detect a failure from a psql
script?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Josh Berkus wrote:

> In other words:
>     SAVEPOINT == BEGIN NESTED
>     RELEASE SAVEPOINT == COMMIT NESTED
>     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

Here it should be:
   ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;

And just to clearify, this is an extension to the spec that we then have
to support for a long time. Adding this now and then replacing it with the
standard syntax is not very sexy. If we add this we should support 
it "for ever".

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Dennis Bjorklund wrote:
> On Sat, 10 Jul 2004, Josh Berkus wrote:
> 
> > In other words:
> >     SAVEPOINT == BEGIN NESTED
> >     RELEASE SAVEPOINT == COMMIT NESTED
> >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> 
> Here it should be:
> 
>     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
> 
> And just to clearify, this is an extension to the spec that we then have
> to support for a long time. Adding this now and then replacing it with the
> standard syntax is not very sexy. If we add this we should support 
> it "for ever".

Just to clarify, this is to allow rolling back to the same savepoint
multiple times.  If we named savepoints, the new savepoint would be the
same name as the one we just rolled back.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Dennis, Bruce,

> Just to clarify, this is to allow rolling back to the same savepoint
> multiple times.  If we named savepoints, the new savepoint would be the
> same name as the one we just rolled back.

Hmmm ... yeah, it would be nice to find a way around this so that we don't 
have non-standard behavior we have to work around once savepoint names are 
implemented.   Suggestions?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> Dennis Bjorklund wrote:
> > On Sat, 10 Jul 2004, Josh Berkus wrote:
> > 
> > > In other words:
> > >     SAVEPOINT == BEGIN NESTED
> > >     RELEASE SAVEPOINT == COMMIT NESTED
> > >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> > 
> > Here it should be:
> > 
> >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
> > 
> > And just to clearify, this is an extension to the spec that we then have
> > to support for a long time. Adding this now and then replacing it with the
> > standard syntax is not very sexy. If we add this we should support 
> > it "for ever".
> 
> Just to clarify, this is to allow rolling back to the same savepoint
> multiple times.  If we named savepoints, the new savepoint would be the
> same name as the one we just rolled back.

Sorry, I confused folks.  I should have corrected this line too:

> >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED;

It is not a non-standard behavior.  It is only an implementation detail
used internally that allows nested transactions to implement savepoints.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
I just posted a clarification.  It isn't a problem.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Dennis, Bruce,
> 
> > Just to clarify, this is to allow rolling back to the same savepoint
> > multiple times.  If we named savepoints, the new savepoint would be the
> > same name as the one we just rolled back.
> 
> Hmmm ... yeah, it would be nice to find a way around this so that we don't 
> have non-standard behavior we have to work around once savepoint names are 
> implemented.   Suggestions?
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Bruce,

> Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
> perhaps not allowing easy migration from Oracle.

Well, that's Oracle's problem.  Considering the amount of influence they had 
over the standard, there's no excuse for their syntax.     Also, if someone 
converts and Oracle script which does not do  RELEASE, it's still ok with us; 
they just end up nesting multiple levels and not "releasing" until the main 
transaction is committed.

> Don't we see the error from libpq PQexec() return value and other
> interfaces?  

As far as I know, DBD::pg does not at this time; it detects an error but does 
not return the SQLSTATE, and I'm *sure* that PHP 4 does not.  I'm sure there 
are other interfaces in the same boat.   And nobody has answered the question 
of what SQLSTATE ranges indicate an abort state as opposed to something else 
-- I get the feeling that this is not at all defined.

> Are you saying how do we detect a failure from a psql
> script?

Right.  There are applications out there:  shell scripts, ODBC applications, 
etc., which are unlikely to *ever* have the ability to read states from 
libpq.  These applications need to have the ability to detect an abort *by 
query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue 
the proper ROLLBACKs.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Bruce Momjian wrote:

> > > >     SAVEPOINT == BEGIN NESTED
> > > >     RELEASE SAVEPOINT == COMMIT NESTED
> > > >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED
> > > 
> > > Here it should be:
> > > 
> > >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT;
> > > 
> > > And just to clearify, this is an extension to the spec that we then have
> > > to support for a long time. Adding this now and then replacing it with the
> > > standard syntax is not very sexy. If we add this we should support 
> > > it "for ever".
> 
> Sorry, I confused folks.  I should have corrected this line too:
> 
> > >     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED;

Hmm, yes. Correct.

Luckily, we already had: SAVEPOINT == BEGIN NESTED so it all worked out 
:-)

> It is not a non-standard behavior.  It is only an implementation detail
> used internally that allows nested transactions to implement savepoints.

The non-standard part I was talking about was the savepoints without
names, and that is what we should support for ever if we introduce them. 

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Josh Berkus wrote:
> Bruce,
> 
> > Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
> > perhaps not allowing easy migration from Oracle.
> 
> Well, that's Oracle's problem.  Considering the amount of influence they had 
> over the standard, there's no excuse for their syntax.     Also, if someone 
> converts and Oracle script which does not do  RELEASE, it's still ok with us; 
> they just end up nesting multiple levels and not "releasing" until the main 
> transaction is committed.

OK.

> > Don't we see the error from libpq PQexec() return value and other
> > interfaces?  
> 
> As far as I know, DBD::pg does not at this time; it detects an error but does 
> not return the SQLSTATE, and I'm *sure* that PHP 4 does not.  I'm sure there 
> are other interfaces in the same boat.   And nobody has answered the question 
> of what SQLSTATE ranges indicate an abort state as opposed to something else 
> -- I get the feeling that this is not at all defined.

They have no way of reporting a failed query back to the user?  How do
people program in those environments?  Right now any failed query aborts
the transaction so it seems it would be pretty easy.

> > Are you saying how do we detect a failure from a psql
> > script?
> 
> Right.  There are applications out there:  shell scripts, ODBC applications, 
> etc., which are unlikely to *ever* have the ability to read states from 
> libpq.  These applications need to have the ability to detect an abort *by 
> query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue 
> the proper ROLLBACKs.

Well, that involves either creating a conditional capability in the
backend, or in psql, neither of which will happen for 7.5.  The best we
can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
ROLLBACK) and just let the script keep going. I am thinking of cases
where you want to drop an object you aren't sure exists in a
transaction.  Anything more complicated like issuing a replacement query
will have to wait for 7.6.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Bruce,

> They have no way of reporting a failed query back to the user?  How do
> people program in those environments?  Right now any failed query aborts
> the transaction so it seems it would be pretty easy.

Believe it or not, PHP4 doesn't.   This is one of the reasons why coders in 
other languages don't consider PHP a "real" programming language; the lack of 
exception handling.   However, given this limitation we can't really use NTs 
in PHP4 anyway, so it's sort of a moot point.  Sorry for bringing it up.

To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the 
DBD::Pg driver reports back a query exception, but not the SQLSTATE.  This 
means that we can detect an abort (assuming all exceptions are aborts) but 
not what caused the abort, except by parsing the error message for text -- a 
hazardous approach at best.  But you would be right to point out that this is 
a problem with the DBD::Pg driver.

There are, however, other client applications where the problem is more 
ingrained.   I've done an application for ColdFusion recently, and discovered 
that CF is completely unable to detect even the limited error-reporting 
capability of ODBC.   This means that if CF can't query it, it doesn't exist.

> Well, that involves either creating a conditional capability in the
> backend, or in psql, neither of which will happen for 7.5.  The best we
> can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> ROLLBACK) and just let the script keep going. I am thinking of cases
> where you want to drop an object you aren't sure exists in a
> transaction.  Anything more complicated like issuing a replacement query
> will have to wait for 7.6.

OK, I didn't realize that it was a difficult thing.   I think it should go on 
the TODO list but you are the judge of what's a quick fix and what's not.

(BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE 
ERROR)

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Dennis,

> The non-standard part I was talking about was the savepoints without
> names, and that is what we should support for ever if we introduce them. 

I don't have a problem with that idea.    Anonymous Savepoints should be easy 
to support if we are supporting Named (spec) Savepoints.  And the two should 
even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts 
with a different syntax would.   And, it's also a convenient shortcut for the 
most common case -- transactions with 1 level of nesting and only a couple of 
non-overlapping savepoints.

Of course, if Alvaro can knock out Named Savepoints in a week, then sure, 
let's go for it.  But I've not heard him saying he can.

However, this does bring up an important issue; if we implement anonymous 
savepoints, then should the current implementation accept savepoint names and 
just ignore them?    If not, it makes porting and coding for the spec much 
more difficult; if so, ported applications could develop subtle erroneous 
behaviour through wrong rollbacks.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Josh Berkus wrote:
> Bruce,
> 
> > They have no way of reporting a failed query back to the user?  How do
> > people program in those environments?  Right now any failed query aborts
> > the transaction so it seems it would be pretty easy.
> 
> Believe it or not, PHP4 doesn't.   This is one of the reasons why coders in 
> other languages don't consider PHP a "real" programming language; the lack of 
> exception handling.   However, given this limitation we can't really use NTs 
> in PHP4 anyway, so it's sort of a moot point.  Sorry for bringing it up.
> 
> To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the 
> DBD::Pg driver reports back a query exception, but not the SQLSTATE.  This 
> means that we can detect an abort (assuming all exceptions are aborts) but 
> not what caused the abort, except by parsing the error message for text -- a 
> hazardous approach at best.  But you would be right to point out that this is 
> a problem with the DBD::Pg driver.
> 
> There are, however, other client applications where the problem is more 
> ingrained.   I've done an application for ColdFusion recently, and discovered 
> that CF is completely unable to detect even the limited error-reporting 
> capability of ODBC.   This means that if CF can't query it, it doesn't exist.

Well, I don't think we need exception handling to support failed
transactions.  Don't these function calls return some failure result
code?

> > Well, that involves either creating a conditional capability in the
> > backend, or in psql, neither of which will happen for 7.5.  The best we
> > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> > ROLLBACK) and just let the script keep going. I am thinking of cases
> > where you want to drop an object you aren't sure exists in a
> > transaction.  Anything more complicated like issuing a replacement query
> > will have to wait for 7.6.
> 
> OK, I didn't realize that it was a difficult thing.   I think it should go on 
> the TODO list but you are the judge of what's a quick fix and what's not.

Adding something to psql or the backend like IF (ERROR) ... would be a
big job, I would think.

> (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE 
> ERROR)

The syntax was for support of script languages that don't have
conditional constructs, like psql scripts, where you want the subxact to
commit but if it fails, you don't want that to affect the outer
transaction.  Are you saying there are very few cases where you don't
care if the subxact commits or aborts?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Josh Berkus wrote:
> Dennis,
> 
> > The non-standard part I was talking about was the savepoints without
> > names, and that is what we should support for ever if we introduce them. 
> 
> I don't have a problem with that idea.    Anonymous Savepoints should be easy 
> to support if we are supporting Named (spec) Savepoints.  And the two should 
> even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts 
> with a different syntax would.   And, it's also a convenient shortcut for the 
> most common case -- transactions with 1 level of nesting and only a couple of 
> non-overlapping savepoints.
> 
> Of course, if Alvaro can knock out Named Savepoints in a week, then sure, 
> let's go for it.  But I've not heard him saying he can.

It seems anonymous savepoints really don't buy us anything.  They don't
match the Oracle behavior, and don't do anything more than nested
transactions. I agree we want them, but I don't see the value they add
value right now.

> However, this does bring up an important issue; if we implement anonymous 
> savepoints, then should the current implementation accept savepoint names and 
> just ignore them?    If not, it makes porting and coding for the spec much 
> more difficult; if so, ported applications could develop subtle erroneous 
> behaviour through wrong rollbacks.

I don't see how we can ignore the savepoint names without having our
code work unpredicatably.  We could check for the most recent savepoint
name and error out if they reference any other name than the most recent
savepoint.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Peter Eisentraut
Date:
Josh Berkus wrote:
>     But the ability to detect an abort state *from the SQL command line*
> (or a database port connection) has not been addressed.

This has existed since 7.4.  If some interfaces don't expose it, fix 
those interfaces.



Re: Nested Transactions, Abort All

From
Andreas Pflug
Date:
Bruce Momjian wrote:

>
>The syntax was for support of script languages that don't have
>conditional constructs, like psql scripts, where you want the subxact to
>commit but if it fails, you don't want that to affect the outer
>transaction.  Are you saying there are very few cases where you don't
>care if the subxact commits or aborts?
>
>  
>
Trying to enable nested transaction on something that has no 
conditionals seems strange to me. If you're writing an app so 
complicated you so you need NTs, you'd probably not code is as psql script.

BTW, do we have real world examples of apps that are waiting to be 
ported to pgsql, needing nested transactions? Looking at the coding 
constructions used in those apps could help deciding what semantics 
would help them.

Compiere comes to my mind, being oracle now, so they'd probably prefer 
named savepoints.

Regards,
Andreas




Re: Nested Transactions, Abort All

From
Peter Eisentraut
Date:
Bruce Momjian wrote:
> It seems anonymous savepoints really don't buy us anything.  They
> don't match the Oracle behavior, and don't do anything more than
> nested transactions. I agree we want them, but I don't see the value
> they add value right now.

The value they add is that they follow the SQL standard, which is a lot 
better sell than "proprietary transaction management scheme".  Those 
people who think they can redefine the SQL standard for purely 
aesthetic reasons have paid the price over and over again.



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Bruce,

> It seems anonymous savepoints really don't buy us anything.  They don't
> match the Oracle behavior, and don't do anything more than nested
> transactions. I agree we want them, but I don't see the value they add
> value right now.

Anonymous Savepoints == Nested Transactions

This issue is whether we're going to use a PostgreSQL-specific, non-standard, 
syntax for NTs, or use a syntax that puts us on the road to implementing 
spec-compliant savepoints.

Given that the functionality is exactly the same in either case, I don't see 
why you would want to implement syntax which is 100% Postgres-specific.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
Oliver Jowett
Date:
Josh Berkus wrote:

> 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax.   This would 
> have the twin benefit of both allowing us to improve our standards compliance 
> and make savepoints completely compliant in the next version, as well as 
> helping those wishing to migrate from Oracle to PostgreSQL (currently our 
> largest source of migrations).  Its disadvantage is the subtle differences 
> between Alvaro's patch and the standard, which might not be obvious to users 
> and lead to difficult to locate errors.   This option also comes in two 
> flavors:
>     a) we implement savepoint names, troubleshooting the namespace and scoping 
> issues, which would really make this a different feature and delay beta 
> testing, or
>     b) we do anonymous savepoints for now, which more-or-less exactly matches the 
> current behavior of Alvaro's patch, and do complaint, named savepoints in the 
> next version.

As Dennis has said, whatever we do now we should support "for ever". If 
we end up with compliant SAVEPOINT (eventually in 7.6+) plus some 
nonstandard syntax (from 7.5), what is the nonstandard syntax you would 
prefer to see? I'd prefer a syntax that reflects the primitives actually 
in use i.e. BEGIN NESTED.

[...]

> In other words:
>     SAVEPOINT == BEGIN NESTED
>     RELEASE SAVEPOINT == COMMIT NESTED
>     ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

As pointed out by others ROLLBACK TO SAVEPOINT is actually ROLLBACK 
NESTED + BEGIN NESTED. This means that if we only have savepoint syntax, 
there is no way to do a plain rollback of a nested transaction (you have 
to ROLLBACK TO SAVEPOINT foo; RELEASE SAVEPOINT foo which I find pretty 
ugly and nonobvious, and it gives you an extra empty subtransaction)

>     But the ability to detect an abort state *from the SQL command line* (or a 
> database port connection) has not been addressed.

There is a transaction state indicator in the V3 protocol's 
ReadyForQuery message. One of the states is "in aborted transaction". 
It's been around since 7.4.

Whatever logic is needed for running different/conditional SQL based on 
transaction state then belongs on the client side, IMO.

-O


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > It seems anonymous savepoints really don't buy us anything.  They
> > don't match the Oracle behavior, and don't do anything more than
> > nested transactions. I agree we want them, but I don't see the value
> > they add value right now.
> 
> The value they add is that they follow the SQL standard, which is a lot 
> better sell than "proprietary transaction management scheme".  Those 
> people who think they can redefine the SQL standard for purely 
> aesthetic reasons have paid the price over and over again.

Uh, anonymous savepoints aren't in the standard, so we aren't any closer
to the standard with them or without them, and Oracle doesn't have them
either.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Josh Berkus wrote:
> > Well, that involves either creating a conditional capability in the
> > backend, or in psql, neither of which will happen for 7.5.  The best we
> > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
> > ROLLBACK) and just let the script keep going. I am thinking of cases
> > where you want to drop an object you aren't sure exists in a
> > transaction.  Anything more complicated like issuing a replacement query
> > will have to wait for 7.6.
> 
> OK, I didn't realize that it was a difficult thing.   I think it should go on 
> the TODO list but you are the judge of what's a quick fix and what's not.
> 
> (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE 
> ERROR)

OK, no one likes that idea, so let's forget it.

Do we want to allow BEGIN NESTED to start a main transaction?  Oracle
can use SAVEPOINTS all the time because it knows it is always in a
transaction, but PostgreSQL is not always.  I don't see a downside to
allowing it.  COMMIT will still commit the entire transaction, of
course.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
On Sat, 2004-07-10 at 15:21, Josh Berkus wrote:
> Bruce,
> 
> > They have no way of reporting a failed query back to the user?  How do
> > people program in those environments?  Right now any failed query aborts
> > the transaction so it seems it would be pretty easy.
> 
> Believe it or not, PHP4 doesn't.   This is one of the reasons why coders in 
> other languages don't consider PHP a "real" programming language; the lack of 
> exception handling.   However, given this limitation we can't really use NTs 
> in PHP4 anyway, so it's sort of a moot point.  Sorry for bringing it up.

Uh, I think it can:

http://www.php.net/manual/en/function.pg-result-error.php





Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sat, Jul 10, 2004 at 08:25:16PM -0400, Bruce Momjian wrote:

> Do we want to allow BEGIN NESTED to start a main transaction?

I have a better question: do we allow SAVEPOINT (i.e. to establish a
savepoint, i.e. to open a nested transaction) within an aborted
transaction block?

This is allowed in nested transactions, so:

begin; select 0/0;        -- aborts begin;   select 1;        -- the usual "commands will be ignored till the end"
commit;
commit;                -- it really rolls back

But in savepoints it's not clear that we want to allow to establish a
savepoint, so do you prefer

begin; select 0/0; savepoint foo;    -- "commands will be ignored" select 1;            -- "commands will be ignored"
releasefoo;        -- "commands will be ignored"
 
commit;                -- it really rolls back


Or

begin; select 0/0; savepoint foo;    -- executes it select 1;            -- "commands will be ignored" release foo;
  -- executes it
 
commit;                -- it really rolls back


There is not a lot of difference.  This was allowed in nested
transactions because we wanted the nesting be to OK when using it in a
possibly aborted transaction block, so the user would not commit a
transaction that could not have been created.  In savepoints it's a
nonissue because the command to end the outer xact is different.


My opinion is that we should disallow both SAVEPOINT and RELEASE when in
an aborted transaction block.  Only ROLLBACK TO, ROLLBACK and COMMIT
would be allowed.  In this scenario, ROLLBACK TO would always return to
a non-aborted transaction state, or the target savepoint would not
exist and the state would remain the same.

There are several places where the code could be made simpler with this.
Opinions please?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)



Re: Nested Transactions, Abort All

From
Dennis Bjorklund
Date:
On Sat, 10 Jul 2004, Bruce Momjian wrote:

> Oracle can use SAVEPOINTS all the time because it knows it is always in
> a transaction, but PostgreSQL is not always.

PostgreSQL is also alsways in a transaction. If some use autocommit and go
 SAVEPOINT foo;
 RELEASE foo;

The first will work and that transaction will end. Then the next is in a
new transaction and will fail with an error saying that foo does not
exist. That's how it should be.

If people don't like or understand autocommit they should not use it.  
This is especially true in other cases where people do updates that really
must be in a single transaction.

Your idea is that if we use nested BEGIN/COMMIT one can always issue these
even if one does not know if one have done BEGIN before or not. To me this
is a problem with autocommit that is solved by not using autocommit. I 
don't think this is a problem we should solve.

-- 
/Dennis Björklund



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Scott,
> Uh, I think it can:
> 
> http://www.php.net/manual/en/function.pg-result-error.php

Heh.  I half-knew that if I pointed this out that someone would correct me 
with a link to new code.   In my defense, I will point out that the mentioned 
PHP feature is less than 4 months old.

> Not a real language indeed.  :-)

<grin>  I hope you relize that that was said as someone who uses PHP for a lot 
of projects ...

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Bruce,

> Do we want to allow BEGIN NESTED to start a main transaction?  Oracle
> can use SAVEPOINTS all the time because it knows it is always in a
> transaction, but PostgreSQL is not always.  I don't see a downside to
> allowing it.  COMMIT will still commit the entire transaction, of
> course.

Hmmm.   I can see where this could cause trouble, allowing users and 
developers to be unclear about whether or not they are in an explicit 
transaction and thus leading to significant debugging issues.   So I'm not 
keen on, it, no.    

What's the benefit?  Elein?

And before you start the "function" argument:  due to function autocommit, a 
function is automatically part of a main implict transaction.  So functions 
are a non-argument as they will *always* be using NESTED/SAVEPOINT.   This 
would only become a concern if we started supporting non-transactional stored 
procedures (ala Sybase) which nobody has even discussed working on.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
Josh Berkus
Date:
Alvaro,

> I have a better question: do we allow SAVEPOINT (i.e. to establish a
> savepoint, i.e. to open a nested transaction) within an aborted
> transaction block?

My opinion?  No.   I would personally not want to allow it.

> My opinion is that we should disallow both SAVEPOINT and RELEASE when in
> an aborted transaction block.  Only ROLLBACK TO, ROLLBACK and COMMIT
> would be allowed.

I agree.

> In this scenario, ROLLBACK TO would always return to
> a non-aborted transaction state, or the target savepoint would not
> exist and the state would remain the same.

This is also good.

From my perspective, as a builder of some *very* database-centric
applications, if one has an abort contidition that proceeds to try to
establish a Savepoint as if the abort didn't exist then one needs to do some
debugging.  I'm sorry I missed the original discussion on this or I would
have expressed this opinion earlier.

For that matter:

begin;savepoint;    select 0/0;  -- abort    savepoint;  -- commands will be ignored        select 1; -- commands will
beignored    release; -- commands will be ignoredrelease; -- abort main xact 'cause we didn't rollback 
commit; -- abort message

Is the above more or less correct, Alvaro?

--
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Nested Transactions, Abort All

From
"Scott Marlowe"
Date:
On Sun, 2004-07-11 at 16:01, Josh Berkus wrote:
> Scott,
>  
> > Uh, I think it can:
> > 
> > http://www.php.net/manual/en/function.pg-result-error.php
> 
> Heh.  I half-knew that if I pointed this out that someone would correct me 
> with a link to new code.   In my defense, I will point out that the mentioned 
> PHP feature is less than 4 months old.

Actually, it's part of PHP since 4.2.0, which was released on 22 April
2002.  That's long enough most folks should know of it by now.

(see http://www.php.net/releases.php)

> > Not a real language indeed.  :-)
> 
> <grin>  I hope you relize that that was said as someone who uses PHP for a lot 
> of projects ...

I know you do, I'm just amazed at how many people will dog PHP when it's
not the same language they downloaded and tried 5 years ago :-(



Re: Nested Transactions, Abort All

From
Alvaro Herrera
Date:
On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote:

> For that matter:
> 
> begin;
>     savepoint;
>         select 0/0;  -- abort
>         savepoint;  -- commands will be ignored
>             select 1; -- commands will be ignored
>         release; -- commands will be ignored
>     release; -- abort main xact 'cause we didn't rollback
> commit; -- abort message
> 
> Is the above more or less correct, Alvaro?

Save a minor detail.  It would be

begin;savepoint;    select 0/0;  -- abort    savepoint;  -- commands will be ignored    select 1; -- commands will be
ignored   release; -- commands will be ignored    release; -- commands will be ignored
 
commit; -- abort message

Note that I'm trying to tell you something with the indenting; all those
commands are inside one and the same subtransaction.

And I'm not planning to do anonymous savepoint.  Do these buy us
anything?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)



Re: Nested Transactions, Abort All

From
Bruce Momjian
Date:
Alvaro Herrera wrote:
> On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote:
> 
> > For that matter:
> > 
> > begin;
> >     savepoint;
> >         select 0/0;  -- abort
> >         savepoint;  -- commands will be ignored
> >             select 1; -- commands will be ignored
> >         release; -- commands will be ignored
> >     release; -- abort main xact 'cause we didn't rollback
> > commit; -- abort message
> > 
> > Is the above more or less correct, Alvaro?
> 
> Save a minor detail.  It would be
> 
> begin;
>     savepoint;
>         select 0/0;  -- abort
>         savepoint;  -- commands will be ignored
>         select 1; -- commands will be ignored
>         release; -- commands will be ignored
>         release; -- commands will be ignored
> commit; -- abort message
> 
> Note that I'm trying to tell you something with the indenting; all those
> commands are inside one and the same subtransaction.
> 
> And I'm not planning to do anonymous savepoint.  Do these buy us
> anything?

Don't bother if you can do named ones.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Nested Transactions, Abort All

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> To my current knowledge (and hopefully Andrew will speak up if I'm
> wrong) the DBD::Pg driver reports back a query exception, but not
> the SQLSTATE.
The current production driver will report back the error, but not the
SQLSTATE. The next version (now in cvs) will report back the SQLSTATE.
It will probably go beta in a couple of weeks and eventually become
version 1.33.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200407102121
-----BEGIN PGP SIGNATURE-----
iD8DBQFA8e5QvJuQZxSWSsgRAo5TAKDD1OX5xV4mfyUC8RAt+0SA8gbjiACeJIFV
2rZfNrm9OTFJ+/kzAjUiMJM=
=TkiX
-----END PGP SIGNATURE-----




Re: Nested Transactions, Abort All

From
Jan Wieck
Date:
On 7/10/2004 6:55 PM, Josh Berkus wrote:

> Bruce,
> 
>> It seems anonymous savepoints really don't buy us anything.  They don't
>> match the Oracle behavior, and don't do anything more than nested
>> transactions. I agree we want them, but I don't see the value they add
>> value right now.
> 
> Anonymous Savepoints == Nested Transactions

Almost

> 
> This issue is whether we're going to use a PostgreSQL-specific, non-standard, 
> syntax for NTs, or use a syntax that puts us on the road to implementing 
> spec-compliant savepoints.
> 
> Given that the functionality is exactly the same in either case, I don't see 
> why you would want to implement syntax which is 100% Postgres-specific.
> 

I don't think they are 100% the same. The SQL3 spec defines in 7.15 and 
13.4 that each sql procedure statement and each subquery on close 
implicitly destroy all savepoints that have been created during that 
statement or subquery.

I am however certain that nested transactions do not offer any 
additional functionality that would not be available through savepoints. 
So what I am missing is the reason why we would want a non-standard 
syntax at all. Especially using the keyword BEGIN in the syntax would 
strike me as dumb, because it will create a parsing and reading 
nightmare for PL/pgSQL, since that language uses BEGIN ... END; for 
grouping statements like C uses curly braces.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Re: Nested Transactions, Abort All

From
Jan Wieck
Date:
On 7/11/2004 12:22 AM, Alvaro Herrera wrote:

> There is not a lot of difference.  This was allowed in nested
> transactions because we wanted the nesting be to OK when using it in a
> possibly aborted transaction block, so the user would not commit a
> transaction that could not have been created.  In savepoints it's a
> nonissue because the command to end the outer xact is different.
> 
> 
> My opinion is that we should disallow both SAVEPOINT and RELEASE when in
> an aborted transaction block.  Only ROLLBACK TO, ROLLBACK and COMMIT
> would be allowed.  In this scenario, ROLLBACK TO would always return to
> a non-aborted transaction state, or the target savepoint would not
> exist and the state would remain the same.

As I interpret the spec ROLLBACK TO foo will rollback all savepoints 
that have been created since savepoint foo was created including ones 
explicitly released. That means, that every subxid >= foo is aborted, 
and a new foo subtransaction created.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Re: Nested Transactions, Abort All

From
Mike Rylander
Date:
<posted & mailed>

Dennsnippetssklund wrote:

> On Fri, 9 Jul 2004, Mike Rylander wrote:
> 
>> Nested transactions and savepoints serve two different purposes.  They
>> have some overlap, but for the most part solve two distinct problems.
> 
> Then show some examples that illustrait the difference. So far all
> examples shown that uses subtransactions could just as well have been
> written using savepoints.
> 

After seeing some more snippets of the SQL2003 spec it seems that this is
true, and that there is more of a syntactic difference than functional.
This does not seem to be the case for Oracle (the other major
implementation that has been cited for SAVEPOINT syntax), as savepoints in
Oracle are not logically nested.  Note that I am going on the statements
from others on this list for this point...

> I don't agree that they have two different purposes.

They do, if only to make particular constructs easier to write.  This is an
opinion, but for example an EXCEPTION framework for plpgsql would be easier
to implement and use if it used the nested transactions rather than
savepoint syntax:

CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
BEGIN       BEGIN NESTED;               do some work...               BEGIN NESTED;                       do other
work...              EXCEPTION WHEN SQLSTATE = already_exists THEN                       do alternate work with its own
errorchecking...               END NESTED;       EXCEPTION WHEN SQLSTATE = fkey_violation THEN               ROLLBACK
NESTED;      END NESTED;
 
END;';

I realize this can be done with nested savepoints and that is what the spec
requires, but in other major implementations of savepoints this nested
exception handling would be more difficult to write.  Again, simply my
opinion.

> 
>> I don't think so, especially as there has been some talk of implementing
>> savepoints as a subset of nested transactions.
> 
> It is not a subset. It's the other way around. Nested transactions are a
> subset of savepoints

Perhaps I got my wires crossed a bit there.  And again, after looking at
some more of the SQL2003 spec this does seem to be the case.  I cry your
pardon! :)

> 
> Savepoints have more possibilities, you can invalidate older savepoints
> then the last (with subtransactions you can only commit/rollback the
> last).

This implies that savepoints are flat.  It won't be that way under the
covers, but it does give that impression, and flat savepoint space is
definitely suited to a different class of problems than nested
transactions.

> If you don't use that then it's exactly the same as 
> subtransactions.
> 

I don't see this.  Nested transactions present a hierarchal interface to the
user, savepoints don't, especially considering that those familiar with
PL/SQL know that savepoints are not nested.  Now, savepoints can be used IN
a hierarchy, but they do not DEFINE one as nested transactions do.

I look at it this way: Let's have both, and where a user wants a flat
transaction space, as that may suit the needs of the problem, they will use
SAVEPOINT syntax; if the user would perfer an explicit hierarchy they can
use nested transactions.  Everyone wins!

> The only "feature" subtransactions have that savepoints doesn't is the
> lack of names. Every savepoint have a name. If we want an extension it
> could be to get the database to generate a fresh savepoint name. The
> client can of course also generate unique savepoint names if it want.

I don't think they can be compared like that, feature for feature.  Although
I agree with you that they provide extremely similar feature sets, the
present different interfaces to the user.  They may end up being backed by
the exact same code but the syntax and logical structure will surely
differ, and when a user wants labeled rollback point they will use
savepoints.  When s/he wants hierarchical rollback points they will use the
nested transactions syntax.

BTW, I would imagine that savepoints will be implemented as nested
transactions with detachable labels... the label can move from a
transaction to one of its descendants, and that outer (sub)transaction will
be implicitly COMMITed with its parent.

> 
> That subtransactions do more than savepoints is just smoke an mirrors. So
> far there have been no example to validate that point of view, and I don't
> think there will be any. If anyone know of something that you can do with
> subtransactions and not with savepoints, please speak up.
> 

You have opened my eyes to the fact that savepoints and nested transactions
can be used for most of the same problems, however I don't see this as a
one-or-the-other proposition.

Alvaro found it easier to implement nested transactions, he forged ahead and
did it.  Now, because of good design or simple luck, we should be able to
implement savepoints fairly easily.  To me this is the best we could have
hoped for, as it means that not only will be support the entire SQL2003
spec WRT savepoints, we actually get to present a richer interface to the
user, one that includes a feature explicitly designed to model the
hierarchical nature of certain datasets and/or solutions.  Of course anyone
reading this can guess which interface I am looking forward to, but the
point is that we will have both where most others don't have a complete
implementation of either!

--miker



Re: Nested Transactions, Abort All

From
Mike Rylander
Date:
<posted & mailed>

Dennis Bjorklund wrote:

> On Sat, 10 Jul 2004, Mike Rylander wrote:
> 
>> They do, if only to make particular constructs easier to write.  This is
>> an opinion, but for example an EXCEPTION framework for plpgsql would be
>> easier to implement and use if it used the nested transactions rather
>> than savepoint syntax:
>> 
>> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
>> BEGIN
>>         BEGIN NESTED;
>>                 do some work...
>>                 BEGIN NESTED;
>>                         do other work...
>>                 EXCEPTION WHEN SQLSTATE = already_exists THEN
>>                         do alternate work with its own error checking...
>>                 END NESTED;
>>         EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>>                 ROLLBACK NESTED;
>>         END NESTED;
>> END;';
>> 
>> I realize this can be done with nested savepoints and that is what the
>> spec requires,
> 
> Lets look at what it can look like:
> 
> BEGIN
>           SAVEPOINT nested;
>                   do some work...
>                   SAVEPOINT nested2;
>                           do other work...
>                   EXCEPTION WHEN SQLSTATE = already_exists THEN
>                           ROLLBACK TO SAVEPOINT nested2;
>                           do alternate work with its own error checking...
>                   RELEASE nested2;
>           EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>                   ROLLBACK TO SAVEPOINT nested;
>           RELEASE nested;
> END;
> 
> 
> Now, in what way is this more complicated?

Only in that you need to define a name for each savepoint in order to create
the hierarchy.  And that is my point, savepoints impose more work on the
user to create a logical hierarchy, not that they cannot be used for
hierarchical structures.

> 
> I'm not 100% sure how the exceptions that you used above work. Do that
> always rollback the transaction thay are in? In one of the exceptions you
> did a rollback but not in the other. In my example I added a rollback in
> the first exception handler. Maybe you forgot it there?

That was just pseudo-code and wholly invented in my head, but based on an
earlier expample of possible EXCEPTION syntax.  The idea is that when a
subtransaction is in an aborted state due to an error the EXCEPTION clause
would implicitly roll back that subtransaction and open a new transaction
from its own block.  This EXCEPTION subtrans is only used in the case of an
error in the matching BEGIN NESTED block, and the two share the COMMIT
statement, syntacticly speaking.  Think of it as a "try { ... } catch
[type] { ... } finally { commit }" type structure.

> 
> In any case. I don't see this as any harder then your example.
> 

It's not harder, per se, but it does impose a more difficult to maintain
syntax, IMHO.

>> > Savepoints have more possibilities, you can invalidate older savepoints
>> > then the last (with subtransactions you can only commit/rollback the
>> > last).
>> 
>> This implies that savepoints are flat.  It won't be that way under the
>> covers, but it does give that impression, and flat savepoint space is
>> definitely suited to a different class of problems than nested
>> transactions.
> 
> First, my claim above was wrong. As Gavin pointed out in another mail, if
> one have savepoints p1 and p2 and release p1 then also p2 is released.
> It's possible to implement both kinds of behaviour using Alvaros work, but
> the standard demands the simpler one where p2 is also released.
> 
> Now, about the flatness. Savepoints are not flat. They are sort of flat in
> a savepoint level. But, for example when you call a function you get a new
> savepoint level. I actually don't want to call it flat at all. The example
> above does not overwrite the savepoints "nested" and "nested2" that might
> exist before the call, since this is a higher savepoint level.
> 

OK, savepoints are not REALLY flat, but they are not hierarchically nested
either.  They are cumulative.  They can be used, as you showed above, in a
hierarchy, but as I said, they are not by their nature "nested".

> I'm not sure exactly what it is that defines a new savepoint level, but a
> function call does and maybe some other things.
> 

As for savepoint levels in functions, that is a scoping issue imposed by the
functions themselves, not by the savepoint syntax.  It would be nonsensical
to rollback to a savepoint outside a function, just as it would be
nonsensical to rollback the outer transaction from within the function. 
Allowing either would cause undesired "action at a distance" and possibly
violate the A in ACID.  The way I see it, savepoint levels should be
specified by function calls, as you said, and by the transaction nesting
level.

>> BTW, I would imagine that savepoints will be implemented as nested
>> transactions with detachable labels... the label can move from a
>> transaction to one of its descendants, and that outer (sub)transaction
>> will be implicitly COMMITed with its parent.
> 
> Yes. That's my view as well.
> 

Well, at least we agree on that ;)

>> Alvaro found it easier to implement nested transactions, he forged ahead
>> and
>> did it.  Now, because of good design or simple luck, we should be able to
>> implement savepoints fairly easily.
> 
> I think the difference between them are so small that it's not a big deal
> at all. In my view savepoints and nested transactions are almost the same
> thing. The only difference being that the savepoints have names.
> Savepoints are nested. You can not have savepoints p1 and then p2 and try
> to only rollback p1. Then you rollback p2 as well, why. Because they are
> nested.
> 

Well, at this point there is a great difference when compared to other
implementations.  And, in reality, that is our competition.  The spec is
there to "level the playing field", as it were.  And with a nested
transaction-backed implementation of savepoints we will be closer to the
goal line than our competition.

>> spec WRT savepoints, we actually get to present a richer interface to the
>> user
> 
> If it's richer or not is the question. And then one have to compare that
> to the downside of adding a non standard interface.
> 

And the upside, which I consider great.  I could see it becoming an
implementation leader for others to follow.

> I don't think it is richer at all, but I'd be happy to change my mind if
> someone can show an example where nested transactions solve something that
> you can't just as well solve with savepoints.
> 

It is yet to be seen if nested transactions in PG will be everythink I hope
they can be, and perhaps the benefits will indeed be individually
qualitative, instead of globally quantitative.

Therefore, I don't know if I can show empirically that having user-exposed
nested transaction is "better" because it comes down to individual choice
of style.  I can definitely see nested transactions containing (and
defining the level of) savepoints as being a HUGE boon to the logical
maintainability of stored procedures and long running, recurring scripts.

The end result will be more tools in the hands of users.  I am sure the docs
will explain that (currently) PG nested transactions are an extention to
the standard.

-- 
--miker