Re: Nested Transactions, Abort All

Simon Riggs wrote:

>ISTM - my summary would be
>1. We seem to agree we should support SAVEPOINTs
>
>2. We seem to agree that BEGIN/COMMIT should stay unchanged...
>
>
>
>>With savepoints, it looks pretty strange:
>>
>>BEGIN;
>> SAVEPOINT x1;
>> INSERT INTO ...;
>> SAVEPOINT x2;
>> INSERT INTO ...;
>> SAVEPOINT x3;
>> INSERT INTO ...;
>>
>>
>>
>
>This isn't how you would use SAVEPOINTs...look at this...
>
>BEGIN
> display one screen to user - book the flight
> INSERT INTO ...
> INSERT INTO ...
> UPDATE ...
> SAVEPOINT
> display another related screen - book the hotel
> INSERT INTO
> DELETE
> UPDATE
> UPDATE
> SAVEPOINT
> offer confirmation screen
>COMMIT (or ROLLBACK)
>
>

No, SAVEPOINT is not some kind of intermediate commit, but a point where
a rollback can rollback to.
Look at this oracle stuff when googling for SAVEPOINT ROLLBACK:

BEGIN
SAVEPOINT before_insert_programmers;
insert_programmers (p_deptno);
EXCEPTION
WHEN OTHERS THEN ROLLBACK TO before_insert_programmers;
END;

There's no need for an intermediate commit, because the top level
rollback would overrule it (if not, it would be an independent
transaction, not nested).

I'd opt for BEGIN as a start of a subtransaction (no need for special
semantics in plpgsql), the corresponding END simply changes the
transaction context to the parent level.
BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT
<name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT] <name>.
For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could be used.
This would be an extension to oracle's usage, which seems quite
reasonable to me.

Regards,
Andreas