Re: Nested Transactions, Abort All

Dennis Bjorklund wrote:

>>Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would
>>seem to always require a name.
>
> Yes, it does. But surely they can be nested so an inner use of name foo
> hides an outer use of name foo. I'm not pretending to know all about the
> standard savepoints, so I just assume they can be nested.

The specs appear to say that reuse of a savepoint name moves the name
rather than hiding it. There's also a concept of a savepoint level which
seems to be essentially a namespace for savepoints, and provision for
entering a new savepoint level during a call to a SQL function.

> I'd like to use the ansi standard and hopefully portable syntax. I don't
> see any real gains by having our own syntax. If the goal is just to save
> some tokens I definetly see no reason. There might still be something more
> to subtransactions, but I really have not seen it.

My concern is that if we are building savepoints on top of nested
subtransactions -- which is the approach so far -- then treating that
system as something that only provides savepoints is counterproductive.

Consider:

SAVEPOINT point1
-- work 1
-- maybe ROLLBACK TO point1
SAVEPOINT point2
-- work 2
-- maybe ROLLBACK TO point2
SAVEPOINT point1
-- work 3
-- maybe ROLLBACK TO point1
SAVEPOINT point2
-- work 4
-- maybe ROLLBACK TO point2

-- repeat ad nauseam

On the surface this looks cheap if you treat the transaction model as
one flat transaction with N savepoints (which is what SAVEPOINT seems to
be about doing, looking at it independent of an implementation) --
there are only two savepoints active at any particular point. But if the
underlying model is actually nested transactions, you are going to end
up with a very large number of active nested transactions, since at the
point the server sees the reuse of 'point1' it's too late to commit the
transaction maintaining that savepoint safely.

This can be fixed by explicit RELEASE SAVEPOINTs after each block of
work, but it's not obvious from the savepoint model why this is needed
-- you only have 2 savepoints active anyway!

Also:

SAVEPOINT point1
DECLARE CURSOR foocursor FOR SELECT * from footable
-- work
RELEASE SAVEPOINT point1
FETCH FORWARD 10 FROM foocursor -- oops, foocursor is no longer open

That behaviour just doesn't fit into the
flat-transaction-with-savepoints model at all.

I guess the question is: are we adding a nested transaction facility or
a savepoint facility? It seems to me we're doing the former, and the
savepoint syntax plus mostly-standard savepoint behaviour is just
compatibility icing. If that's the case, I'd prefer a syntax that
reflects the nested-transaction nature of the beast.

-O