Re: Nested transactions

Alvaro Herrera wrote:

> With this in place, implementing SAVEPOINTs the way SQL expects them to
> work appears to be a very trivial exercise.
>
> You may not see it, but a savepoint is just the start of a nested
> transaction in disguise.  Consider:
>
> begin;
>     insert into foo values (1);
>     savepoint dammit;
>     insert into foo values (2);
>     select foo;        -- fails
>     rollback to dammit;
>     insert into foo values (3);
> commit;
>
> You expect the transaction to finish with tuples 1 and 3 in table foo,
> right?  Well, this is exactly the same as
>
> begin;
>     insert into foo values (1);
>     begin;        -- dammit
>         insert into foo values (2);
>         select foo;    -- fails, goes to aborted state
>     rollback;
>     insert into foo values (3);
> commit;
>
> So all that's needed for the former to work is to be able to define a
> "name" for a transaction (using a cute syntax) and being able to
> rollback to it.  Definitely trivial, after all the work I have put into
> making the latter work.
>

The problem I see with moving towards supporting savepoints with the
current proposal is with how commit works:

Consider:

begin;
      insert into foo values (1);
      savepoint dammit;
      insert into foo values (2);
      select foo;
      insert into foo values (3);
commit;

This one commit needs to commit the top level transaction.  But if the
savepoint command is really starting a sub transaction then that commit
would only commit the subtransaction not the top level transaction.  I
don't see how you can use COMMIT to sometimes mean commit the
subtransaction and other times have it mean commit the top level
transaction.

I don't have a problem with the under the covers functionality in this
patch, it is how begin/commit are changed to support the underlying
functionality that concerns me.  IMHO we should not change the behavior
of begin/commit for nested transactions (leave them do what they have
always done - i.e. control the top level transaction state), but
introduce new syntax for subtransactions control.

thanks,
--Barry