> 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

Fri, 2 Jul 2004 13:14:25 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: Nested Transactions, Abort All
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)


Fri, 2 Jul 2004 13:22:38 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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.



Fri, 02 Jul 2004 13:37:46 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All
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







Fri, 02 Jul 2004 15:33:34 -0400
Mike Mascari <mascarm@mascari.com>
Re: Nested Transactions, Abort All
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)


Fri, 2 Jul 2004 15:38:08 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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.



Fri, 02 Jul 2004 15:32:12 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All
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)


Fri, 2 Jul 2004 17:30:49 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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


Fri, 2 Jul 2004 23:51:01 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Nested Transactions, Abort All
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)


Fri, 2 Jul 2004 18:03:13 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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

Fri, 02 Jul 2004 19:43:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Nested Transactions, Abort All 
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".


Fri, 2 Jul 2004 22:59:11 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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?



Sat, 03 Jul 2004 02:32:44 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All
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)


Mon, 5 Jul 2004 19:42:28 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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


Tue, 6 Jul 2004 08:15:14 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Nested Transactions, Abort All
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, Pennsylvania 19073

Tue, 6 Jul 2004 11:37:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Nested Transactions, Abort All
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)


Tue, 6 Jul 2004 11:56:30 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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)


Tue, 6 Jul 2004 12:25:30 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
* 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
Tue, 6 Jul 2004 12:49:46 -0400
Stephen Frost <sfrost@snowman.net>
Re: Nested Transactions, Abort All
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)


Tue, 6 Jul 2004 13:02:12 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
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.  


Tue, 06 Jul 2004 22:47:18 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: Nested Transactions, Abort All

"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


07 Jul 2004 01:36:53 -0400
Greg Stark <gsstark@mit.edu>
Re: Nested Transactions, Abort All
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


Wed, 7 Jul 2004 08:16:56 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Nested Transactions, Abort All
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

Wed, 07 Jul 2004 19:34:51 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Nested Transactions, Abort All
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


Wed, 7 Jul 2004 10:28:38 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Nested Transactions, Abort All
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

Wed, 07 Jul 2004 20:34:34 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Nested Transactions, Abort All
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


Wed, 7 Jul 2004 10:39:53 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Nested Transactions, Abort All
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 SAVEPOINT s_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

Wed, 07 Jul 2004 20:59:45 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Nested Transactions, Abort All
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


Wed, 7 Jul 2004 11:26:45 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Nested Transactions, Abort All
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 TO point2

   -- 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 an implementation) -- 
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 FORWARD 10 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

Wed, 07 Jul 2004 22:58:06 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Nested Transactions, Abort All
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.



Wed, 07 Jul 2004 09:26:07 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: Nested Transactions, Abort All
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?  



Wed, 07 Jul 2004 09:27:28 -0600
"Scott Marlowe" <smarlowe@qwest.net>
Re: Nested Transactions, Abort All
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.



Wed, 07 Jul 2004 15:50:33 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All