Re: Nested Transactions, Abort All

Alvaro Herrera wrote:

>On Fri, Jul 02, 2004 at 07:43:47PM -0400, Tom Lane wrote:
>
>
>>Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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?