Re: Nested Transactions, Abort All

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.

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