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?