Re: Nested Transactions, Abort All

On Wed, 7 Jul 2004, Oliver Jowett wrote:

> > If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
> > foo; followed by a RELEASE SAVEPOINT foo; 
> 
> Ugh.. nasty syntax and an extra empty transaction.

If you translate it directly using only the primitives of the current 
subbegin/subabort, yes. But that is not the only way to implement it. And 
even if that was the first implementation due to not having time to make 
it better before 7.5, then I still prefer a standard syntax that can be 
improved then a non standard feature to be maintained for all future.

This is about the API to present to the user. The savepoint syntax is
standard, if we should invent our own way it should be for some real
benefit.

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

> One of the use cases for subtransactions was to avoid rollback of the 
> entire transaction if there's an error in a single command -- you wrap 
> each command in a subtransaction and roll it back if it fails. If we 
> only have SAVEPOINT syntax this looks like:
> 
>    -- Success case
>    SAVEPOINT s_12345
>     INSERT INTO foo(...) VALUES (...)
>    RELEASE SAVEPOINT s_12345
> 
>    -- Error case
>    SAVEPOINT s_12346
>     INSERT INTO foo(...) VALUES (...)
>    ROLLBACK TO SAVEPOINT s_12346
>    RELEASE SAVEPOINT s_12346
> 
>    -- Repeat ad nauseam
>
> This is pretty ugly. Given that the underlying mechanism is nested 
> subtransactions,

So you do not want to use the standard syntax in order to save some tokens
in the source?

Also notice that the first and last statement is the same no matter if you
want to rollback or not. So it would be something like (with a nicer
savepoint name then yours):

SAVEPOINT insert;
  INSERT INTO ....
  ... possible more work ...
  if (some_error)     ROLLBACK TO SAVEPOINT insert;

RELEASE SAVEPOINT insert;

I really don't see this as anything ugly with this. Maybe it doesn't fit 
the current implementation, then lets change the implementation and not 
just make an extension that fits a implementation.

> If you don't like adding extra commands, what about extending the 
> standard transaction control commands ("BEGIN NESTED" etc) instead?

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.

At the very least if we add extensions I would like to have a clear and 
stated reason why it should be used instead of the standard feature. Every 
time we add some syntax it has to be maintained forever and we lock in 
users into postgresql. Something I don't like.

-- 
/Dennis Björklund