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