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

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
SAVEPOINTnested2;                         do alternate work with its own error checking...                 RELEASE
nested2;        EXCEPTION WHEN SQLSTATE = fkey_violation THEN                 ROLLBACK TO SAVEPOINT nested;
RELEASEnested;
 
END;


Now, in what way is this more complicated?

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?

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

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

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

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

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

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

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.

-- 
/Dennis Björklund