Re: Nested Transactions, Abort All

Andreas Pflug wrote:

> 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.
>
What happens when you use subtransactions? I think there might be a
visibility issue and how far do you unwind the depth of subtransactions
or transactions?

BEGIN
UPDATE A
SAVEPOINT X
BEGIN
BEGIN
UPDATE B
BEGIN
UPDATE C
ROLLBACK TO SAVEPOINT X
COMMIT
COMMIT
COMMIT
COMMIT

Or

SAVEPOINT X
BEGIN
UPDATE A
ROLLBACK TO SAVEPOINT X
COMMIT