Re: Nested Transactions, Abort All - Mailing list pgsql-hackers

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
TOpoint2
 
  -- 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
animplementation) -- 
 
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
FORWARD10 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