Thread

1. Re: Nested Transactions, Abort All

   Merlin Moncure <merlin.moncure@rcsonline.com> — 2004-07-02T17:14:25Z

   > 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
   
   

2. Re: Nested Transactions, Abort All

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-02T17:22:38Z

   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)
   
   
   

3. Re: Nested Transactions, Abort All

   Thomas Swan <tswan@idigx.com> — 2004-07-02T18:37:46Z

   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.
   
   
   
   

4. Re: Nested Transactions, Abort All

   Mike Mascari <mascarm@mascari.com> — 2004-07-02T19:33:34Z

   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
   
   
   
   
   
   
   
   

5. Re: Nested Transactions, Abort All

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-02T19:38:08Z

   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)
   
   
   

6. Re: Nested Transactions, Abort All

   Thomas Swan <tswan@idigx.com> — 2004-07-02T20:32:12Z

   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.
   
   
   
   

7. Re: Nested Transactions, Abort All

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-02T21:30:49Z

   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)
   
   
   

8. Re: Nested Transactions, Abort All

   Jeroen Vermeulen <jtv@xs4all.nl> — 2004-07-02T21:51:01Z

   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
   
   
   

9. Re: Nested Transactions, Abort All

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-02T22:03:13Z

   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)
   
   
   

10. Re: Nested Transactions, Abort All

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-02T23:43:47Z

    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
    
    

11. Re: Nested Transactions, Abort All

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-03T02:59:11Z

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

12. Re: Nested Transactions, Abort All

    Thomas Swan <tswan@idigx.com> — 2004-07-03T07:32:44Z

    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?
    
    
    
    

13. Re: Nested Transactions, Abort All

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-05T23:42:28Z

    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)
    
    
    

14. Re: Nested Transactions, Abort All

    Dennis Björklund <db@zigo.dhs.org> — 2004-07-06T06:15:14Z

    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
    
    
    

15. Re: Nested Transactions, Abort All

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-07-06T15:37:18Z

    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
    
    

16. Re: Nested Transactions, Abort All

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-06T15:56:30Z

    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)
    
    
    

17. Re: Nested Transactions, Abort All

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-06T16:25:30Z

    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)
    
    
    

18. Re: Nested Transactions, Abort All

    Stephen Frost <sfrost@snowman.net> — 2004-07-06T16:49:46Z

    * 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
    

19. Re: Nested Transactions, Abort All

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-06T17:02:12Z

    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)
    
    
    

20. Re: Nested Transactions, Abort All

    Scott Marlowe <smarlowe@qwest.net> — 2004-07-07T04:47:18Z

    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.  
    
    
    

21. Re: Nested Transactions, Abort All

    Greg Stark <gsstark@mit.edu> — 2004-07-07T05:36:53Z

    "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
    
    
    

22. Re: Nested Transactions, Abort All

    Dennis Björklund <db@zigo.dhs.org> — 2004-07-07T06:16:56Z

    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
    
    
    

23. Re: Nested Transactions, Abort All

    Oliver Jowett <oliver@opencloud.com> — 2004-07-07T07:34:51Z

    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
    
    

24. Re: Nested Transactions, Abort All

    Dennis Björklund <db@zigo.dhs.org> — 2004-07-07T08:28:38Z

    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
    
    
    

25. Re: Nested Transactions, Abort All

    Oliver Jowett <oliver@opencloud.com> — 2004-07-07T08:34:34Z

    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
    
    

26. Re: Nested Transactions, Abort All

    Dennis Björklund <db@zigo.dhs.org> — 2004-07-07T08:39:53Z

    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
    
    
    

27. Re: Nested Transactions, Abort All

    Oliver Jowett <oliver@opencloud.com> — 2004-07-07T08:59:45Z

    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
    
    

28. Re: Nested Transactions, Abort All

    Dennis Björklund <db@zigo.dhs.org> — 2004-07-07T09:26:45Z

    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
    
    
    

29. Re: Nested Transactions, Abort All

    Oliver Jowett <oliver@opencloud.com> — 2004-07-07T10:58:06Z

    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
    
    

30. Re: Nested Transactions, Abort All

    Scott Marlowe <smarlowe@qwest.net> — 2004-07-07T15:26:07Z

    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.
    
    
    
    

31. Re: Nested Transactions, Abort All

    Scott Marlowe <smarlowe@qwest.net> — 2004-07-07T15:27:28Z

    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?  
    
    
    
    

32. Re: Nested Transactions, Abort All

    Thomas Swan <tswan@idigx.com> — 2004-07-07T20:50:33Z

    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.