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