Re: Nested transactions

Barry Lind wrote:
> I agree with Simon's comments. And to them I would add: I had assumed
> that the requirements for 'nested transactions' was following some
> standard definition or specification (i.e. the ANSI SQL spec). But from
> what I can tell, we are rolling our own definition here, not following a
> specification or standard, and not following any of the other major
> commercial databases lead.
>
> I think venturing out on our own and inventing new symantics for
> transactions and sql syntax to support them without giving this a lot of
> thought is bound to lead to problems.
>
> Perhaps I am completely wrong here and there is a clear standard or spec
> that is being implemented, if so, please let me know what that is as it
> would help me in better understanding this patch.
>
> I have been reviewing what Oracle does in this area and it doesn't at
> all resemble what this patch is exposing (especially as far as syntax
> goes). I plan to look at DB2 and MSSQL next.

I realized about three weeks ago that our syntax for nested transactions
doesn't follow anyone else's method. I think I might be the person who
suggested the idea because it seemed more logical to me to allow
BEGIN;BEGIN;COMMIT;COMMIT rather than naming arbitrary locations as
savepoints and doing a rollback to that savepoint name.

And consider this case:

BEGIN;
...
SAVEPOINT x;
SELECT func_call();
SELECT func_call();
COMMIT;

Now if func_call has a savepoint, it is really nested because it can't
know whether the savepoint X will be used to roll back, so its status is
dependent on the status of X. Now, if we used savepoints in func_call,
what happens in the second function call when we define a savepoint with
the same name? I assume we overwrite the original, but using nested
transaction syntax seems much clearer.

Basically, we have to implement this in a nested way. Once it is done,
we can add the window dressing to support the ANSI syntax.

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