Re: Nested transactions

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

Well, because their was only one BEGIN, the commit commits all open
subtransactions.  The code will have to track the number of BEGIN's used
and will have to roll all savepoints into the next commit.  However, it
is only the commit that matches the outermost begin that has this
behavior. Consider this:

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

OK, the inner commit does not close the aa subtransaction.  One big
question is whether it closes the dammit subtransaction.  And is
rollback to aa valid (I think so), and what about rollback dammit, which
was defined in a subtransaction (I think we have to disallow that).

Did I make a mistake by promoting subtransactions rather than
savepoints?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073