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

<posted & mailed>

Dennis Bjorklund wrote:

> On Sat, 10 Jul 2004, Mike Rylander wrote:
> 
>> They do, if only to make particular constructs easier to write.  This is
>> an opinion, but for example an EXCEPTION framework for plpgsql would be
>> easier to implement and use if it used the nested transactions rather
>> than savepoint syntax:
>> 
>> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
>> BEGIN
>>         BEGIN NESTED;
>>                 do some work...
>>                 BEGIN NESTED;
>>                         do other work...
>>                 EXCEPTION WHEN SQLSTATE = already_exists THEN
>>                         do alternate work with its own error checking...
>>                 END NESTED;
>>         EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>>                 ROLLBACK NESTED;
>>         END NESTED;
>> END;';
>> 
>> I realize this can be done with nested savepoints and that is what the
>> spec requires,
> 
> Lets look at what it can look like:
> 
> BEGIN
>           SAVEPOINT nested;
>                   do some work...
>                   SAVEPOINT nested2;
>                           do other work...
>                   EXCEPTION WHEN SQLSTATE = already_exists THEN
>                           ROLLBACK TO SAVEPOINT nested2;
>                           do alternate work with its own error checking...
>                   RELEASE nested2;
>           EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>                   ROLLBACK TO SAVEPOINT nested;
>           RELEASE nested;
> END;
> 
> 
> Now, in what way is this more complicated?

Only in that you need to define a name for each savepoint in order to create
the hierarchy.  And that is my point, savepoints impose more work on the
user to create a logical hierarchy, not that they cannot be used for
hierarchical structures.

> 
> I'm not 100% sure how the exceptions that you used above work. Do that
> always rollback the transaction thay are in? In one of the exceptions you
> did a rollback but not in the other. In my example I added a rollback in
> the first exception handler. Maybe you forgot it there?

That was just pseudo-code and wholly invented in my head, but based on an
earlier expample of possible EXCEPTION syntax.  The idea is that when a
subtransaction is in an aborted state due to an error the EXCEPTION clause
would implicitly roll back that subtransaction and open a new transaction
from its own block.  This EXCEPTION subtrans is only used in the case of an
error in the matching BEGIN NESTED block, and the two share the COMMIT
statement, syntacticly speaking.  Think of it as a "try { ... } catch
[type] { ... } finally { commit }" type structure.

> 
> In any case. I don't see this as any harder then your example.
> 

It's not harder, per se, but it does impose a more difficult to maintain
syntax, IMHO.

>> > Savepoints have more possibilities, you can invalidate older savepoints
>> > then the last (with subtransactions you can only commit/rollback the
>> > last).
>> 
>> This implies that savepoints are flat.  It won't be that way under the
>> covers, but it does give that impression, and flat savepoint space is
>> definitely suited to a different class of problems than nested
>> transactions.
> 
> First, my claim above was wrong. As Gavin pointed out in another mail, if
> one have savepoints p1 and p2 and release p1 then also p2 is released.
> It's possible to implement both kinds of behaviour using Alvaros work, but
> the standard demands the simpler one where p2 is also released.
> 
> Now, about the flatness. Savepoints are not flat. They are sort of flat in
> a savepoint level. But, for example when you call a function you get a new
> savepoint level. I actually don't want to call it flat at all. The example
> above does not overwrite the savepoints "nested" and "nested2" that might
> exist before the call, since this is a higher savepoint level.
> 

OK, savepoints are not REALLY flat, but they are not hierarchically nested
either.  They are cumulative.  They can be used, as you showed above, in a
hierarchy, but as I said, they are not by their nature "nested".

> I'm not sure exactly what it is that defines a new savepoint level, but a
> function call does and maybe some other things.
> 

As for savepoint levels in functions, that is a scoping issue imposed by the
functions themselves, not by the savepoint syntax.  It would be nonsensical
to rollback to a savepoint outside a function, just as it would be
nonsensical to rollback the outer transaction from within the function. 
Allowing either would cause undesired "action at a distance" and possibly
violate the A in ACID.  The way I see it, savepoint levels should be
specified by function calls, as you said, and by the transaction nesting
level.

>> BTW, I would imagine that savepoints will be implemented as nested
>> transactions with detachable labels... the label can move from a
>> transaction to one of its descendants, and that outer (sub)transaction
>> will be implicitly COMMITed with its parent.
> 
> Yes. That's my view as well.
> 

Well, at least we agree on that ;)

>> Alvaro found it easier to implement nested transactions, he forged ahead
>> and
>> did it.  Now, because of good design or simple luck, we should be able to
>> implement savepoints fairly easily.
> 
> I think the difference between them are so small that it's not a big deal
> at all. In my view savepoints and nested transactions are almost the same
> thing. The only difference being that the savepoints have names.
> Savepoints are nested. You can not have savepoints p1 and then p2 and try
> to only rollback p1. Then you rollback p2 as well, why. Because they are
> nested.
> 

Well, at this point there is a great difference when compared to other
implementations.  And, in reality, that is our competition.  The spec is
there to "level the playing field", as it were.  And with a nested
transaction-backed implementation of savepoints we will be closer to the
goal line than our competition.

>> spec WRT savepoints, we actually get to present a richer interface to the
>> user
> 
> If it's richer or not is the question. And then one have to compare that
> to the downside of adding a non standard interface.
> 

And the upside, which I consider great.  I could see it becoming an
implementation leader for others to follow.

> I don't think it is richer at all, but I'd be happy to change my mind if
> someone can show an example where nested transactions solve something that
> you can't just as well solve with savepoints.
> 

It is yet to be seen if nested transactions in PG will be everythink I hope
they can be, and perhaps the benefits will indeed be individually
qualitative, instead of globally quantitative.

Therefore, I don't know if I can show empirically that having user-exposed
nested transaction is "better" because it comes down to individual choice
of style.  I can definitely see nested transactions containing (and
defining the level of) savepoints as being a HUGE boon to the logical
maintainability of stored procedures and long running, recurring scripts.

The end result will be more tools in the hands of users.  I am sure the docs
will explain that (currently) PG nested transactions are an extention to
the standard.

-- 
--miker