Alvaro, Hackers:

I've been giving this some thought.   Here's what I came up with:

We should NOT use the savepoint syntax.   Alvaro's Nested Transactions are not 
savepoints, they don't meet the spec, and they don't behave the same.  Using 
standard syntax for a non-standard feature will, in my opinion, cause more 
confusion than using extension syntax for what is, after all, a 
PostgreSQL-specific feature.

HOWEVER, other databases already have nested transactions.   We could do worse 
than to imitate their syntax; since the syntax we use is arbitrary, we might 
as well pick syntax which minimizes the pain of porting applications.   Of 
the other databases, the most important to imitate for this reason are of 
couse SQL Server and Oracle, since those to cover some 80% of DBAs.

However, Oracle does not support 

SQL Server uses:
Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  Not supported
Rollback inner transaction:  Not supported
Rollback all transanctions:  ROLLBACK { TRANSACTION | WORK }

Please note that, according to the above, MSSQL does not really support nested 
transactions; the inner transactions *cannot* be rolled back, making them 
useless.  There are numerous online discussions about this.

Sybase uses identical syntax, except that Sybase supports Savepoints via an 
extension of the BEGIN/COMMIT syntax:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN TRANSACTION _name_
Commit inner transaction:  COMMIT { TRANSACTION _name_ }
Commit all transactions:  Not supported
Rollback inner transaction:  ROLLBACK TRANSACTION _name_
Rollback all transanctions:  ROLLBACK { TRANSACTION | WORK }

This means that we CANNOT maintain compatibility with other databases without 
supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
I would propose the following syntax:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  COMMIT ALL
Rollback inner transaction:  ROLLBACK { TRANSACTION }
Rollback all transanctions:  ROLLBACK ALL

This would have the flaw of appearing to support SQL Server syntax, while 
actually having a different effect (that is, SQL Server programmers would 
assume that a ROLLBACK would abort everything, but it wouldn't).   If we 
wanted to maintain compatibility in this regard, for easy porting of SQL 
Server applications, we would:

Begin main transaction:   BEGIN { TRANSACTION | WORK }
Begin inner transaction:  BEGIN { TRANSACTION | WORK }
Commit inner transaction:  COMMIT { TRANSACTION | WORK }
Commit all transactions:  COMMIT ALL
Rollback inner transaction:  ROLLBACK NESTED
Rollback all transanctions:  ROLLBACK { TRANSACTION }

... but this puts us in the bad position of supporting somebody else's 
logically inconsistent syntax.    Thoughts?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Thu, 8 Jul 2004 10:40:36 -0700
Josh Berkus <josh@agliodbs.com>
Re: Nested Transactions, Abort All
On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:

> This means that we CANNOT maintain compatibility with other databases without 
> supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
> I would propose the following syntax:
> 
> Begin main transaction:   BEGIN { TRANSACTION | WORK }
> Begin inner transaction:  BEGIN { TRANSACTION | WORK }
> Commit inner transaction:  COMMIT { TRANSACTION | WORK }
> Commit all transactions:  COMMIT ALL
> Rollback inner transaction:  ROLLBACK { TRANSACTION }
> Rollback all transanctions:  ROLLBACK ALL

We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
defined per spec to end the transaction.  So they have to end the
transaction.

Keep in mind that a nested transaction _is not_ a transaction.  You
cannot commit it; it doesn't behave atomically w.r.t. other concurrent
transactions.  It is not a transaction in the SQL meaning of a
transaction.

So, when I say "it has to end the transaction" it cannot just end the
current nested transaction.  It has to end the _real_ transaction.


My proposal would be:

1. Begin main transaction: BEGIN { TRANSACTION | WORK }
2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }

4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }


1, 2 and 3 are not negotiable.  4, 5 and 6 are.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)

Thu, 8 Jul 2004 15:37:27 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
Alvaro,

 > My proposal would be:
 >
 > 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
 > 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
 > 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
 >
 > 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
 > 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
 > 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
 >

I agree with your 1,2 and 3, for the reasons you specify.

I don't like your proposal for 5, because using the keyword COMMIT 
implies something that really isn't true IMHO.  This is due to the fact 
as you point out subtransactions aren't really transactions.  So when 
you 'commit' a subtransaction you are not making the changes permanent 
like a regular transaction.  Instead you are saying these changes are OK 
and the real transaction gets to decide if these changes should be 
committed (or not).  It is only the real transaction that ever does a 
COMMIT (i.e. makes the changes permanent for others to see).  IMHO it is 
for these reasons that the standard SAVEPOINT syntax doesn't have a 
concept of committing a savepoint, only of rolling back to a savepoint.

thanks,
--Barry


Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> 
> 
>>This means that we CANNOT maintain compatibility with other databases without 
>>supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
>>I would propose the following syntax:
>>
>>Begin main transaction:   BEGIN { TRANSACTION | WORK }
>>Begin inner transaction:  BEGIN { TRANSACTION | WORK }
>>Commit inner transaction:  COMMIT { TRANSACTION | WORK }
>>Commit all transactions:  COMMIT ALL
>>Rollback inner transaction:  ROLLBACK { TRANSACTION }
>>Rollback all transanctions:  ROLLBACK ALL
> 
> 
> We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction.  So they have to end the
> transaction.
> 
> Keep in mind that a nested transaction _is not_ a transaction.  You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions.  It is not a transaction in the SQL meaning of a
> transaction.
> 
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction.  It has to end the _real_ transaction.
> 
> 
> My proposal would be:
> 
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
> 
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
> 
> 
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.
> 


Thu, 08 Jul 2004 13:25:49 -0700
Barry Lind <blind@xythos.com>
Re: Nested Transactions, Abort All
Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> 
> > This means that we CANNOT maintain compatibility with other databases without 
> > supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
> > I would propose the following syntax:
> > 
> > Begin main transaction:   BEGIN { TRANSACTION | WORK }
> > Begin inner transaction:  BEGIN { TRANSACTION | WORK }
> > Commit inner transaction:  COMMIT { TRANSACTION | WORK }
> > Commit all transactions:  COMMIT ALL
> > Rollback inner transaction:  ROLLBACK { TRANSACTION }
> > Rollback all transanctions:  ROLLBACK ALL
> 
> We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction.  So they have to end the
> transaction.
> 
> Keep in mind that a nested transaction _is not_ a transaction.  You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions.  It is not a transaction in the SQL meaning of a
> transaction.
> 
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction.  It has to end the _real_ transaction.
> 
> 
> My proposal would be:
> 
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
> 
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
> 
> 
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.

Let me jump in on this.

The initial proposal from Alvaro was to do SUBBEGIN/SUBCOMMIT.  This has
the advantage of allowing BEGIN/COMMIT to commit the entire transaction,
and it is a keyword we can use in plpgsql that doesn't confuse
BEGIN/END.

The disadvantages are:

	o  adds prefix to keyword (SUB) which we don't do other places
	o  doesn't work well with other xact synonyms like BEGIN/END or
	   START TRANSACTION/COMMIT TRANSACTION.

Alvaro wants BEGIN/COMMIT to remain spec-compliant and commit the entire
transaction.  One idea was to do BEGIN NESTED/COMMIT NESTED, but does
that allow plpgsql to use it?  If not, it seems pretty useless.  Imagine:

	BEGIN
		NESTED = 3;

or something like that.

As far as savepoints, yes, we should support them.  Josh is saying our
implementation isn't 100% spec compliant.  In what way does it differ?

As far as implementing only savepoints, look at this:

BEGIN;
	BEGIN;
	INSERT INTO ...;
	COMMIT;
	BEGIN;
	INSERT INTO ...;
	COMMIT;
	BEGIN;
	INSERT INTO ...;
	COMMIT;

With savepoints, it looks pretty strange:
	
BEGIN;
	SAVEPOINT x1;
	INSERT INTO ...;
	SAVEPOINT x2;
	INSERT INTO ...;
	SAVEPOINT x3;
	INSERT INTO ...;

or with RELEASE:

BEGIN;
	SAVEPOINT x1;
	INSERT INTO ...;
	RELEASE SAVEPOINT x1;
	SAVEPOINT x1;
	INSERT INTO ...;
	RELEASE SAVEPOINT x1;
	SAVEPOINT x1;
	INSERT INTO ...;
	RELEASE SAVEPOINT x1;

Yea, I guess it works.  With nested transactions, the SQL mimics the
nested structure of many application languages, while savepoints look
like an add-on to SQL.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 8 Jul 2004 17:56:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Nested Transactions, Abort All
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)

RELEASE SAVEPOINT isn't used that often...

> or with RELEASE:
> 
> BEGIN;
> 	SAVEPOINT x1;
> 	INSERT INTO ...;
> 	RELEASE SAVEPOINT x1;
> 	SAVEPOINT x1;
> 	INSERT INTO ...;
> 	RELEASE SAVEPOINT x1;
> 	SAVEPOINT x1;
> 	INSERT INTO ...;
> 	RELEASE SAVEPOINT x1;
> 

We need to be careful to differentiate from the statement-level abort
behaviour of other RDBMS and the behaviour of SAVEPOINT. It is
theoretically possible to implement them both using nested transactions,
but that doesn't mean they're the same thing.

If a statement has an error, then PostgreSQL currently rolls back the
entire transaction (transaction level abort). It would be good if this
was not the behaviour, since other programs written for other RDBMS do
NOT exhibit this behaviour - this matters a lot if/when statements
error. This behaviour MUST happen implicitly, without additional SQL
statements, otherwise its not the same behaviour. The effect is AS IF
the user had issued the sequence of statements shown above - but they do
not ACTUALLY issue those, hence the reason why the above sequences look
a little wierd.

In the current syntax we're discussing, Oracle's behaviour looks like
this (with all statements in brackets being issued implicitly...) -
using the same example I gave above

BEGIN
	(SUBBEGIN)
		(SUBBEGIN)
			INSERT INTO ...
		(SUBCOMMIT)
		(SUBBEGIN)
			INSERT INTO ...
		(SUBCOMMIT)
		(SUBBEGIN)
			UPDATE ...
		(SUBCOMMIT)
	SUBCOMMIT
	(SUBBEGIN)
		(SUBBEGIN)
			INSERT INTO
		(SUBCOMMIT)
		(SUBBEGIN)
			DELETE
		(SUBCOMMIT)
		(SUBBEGIN)
			UPDATE
		(SUBCOMMIT)
		(SUBBEGIN)
			UPDATE
		(SUBCOMMIT)
	SUBCOMMIT
COMMIT (or ROLLBACK)

Note that you CANNOT choose to rollback the statement you're
executing...it just does so if it fails.

As to whether any of that behaviour is strange... That depends upon your
viewpoints and experience, so I could understand that. The situation
remains....it IS the behaviour and my understanding is that this was the
behaviour we were seeking to emulate?

My confusion with the SAVEPOINT/NESTED debate is - how do you know
whether you SHOULD HAVE issued a SUBBEGIN? When I issue a SAVEPOINT, I
don't care whether or not I've issued a SUBBEGIN before, I just do it
and it works.

The only way to do this seems to be to avoid having a flat nesting
structure, but to have an infinite descent on one part of the nesting
tree...so each statement IMPLICTLY starts with a SUBBEGIN, and SAVEPOINT
and COMMIT just count 'em so they know how many SUBCOMMITs to issue to
get back up again.

e.g.
BEGIN
(SUBBEGIN)
INSERT...
	(SUBBEGIN)
	INSERT....
		(SUBBEGIN)
		INSERT...
SAVEPOINT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT)
(SUBBEGIN)
INSERT...
	(SUBBEGIN)
	INSERT....
		(SUBBEGIN)
		INSERT...
COMMIT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT, COMMIT)


Best Regards, Simon Riggs


Fri, 09 Jul 2004 00:38:32 +0100
Simon Riggs <simon@2ndquadrant.com>
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



Fri, 09 Jul 2004 12:45:04 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
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

    

Fri, 09 Jul 2004 10:38:15 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All
On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:

> 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

What happens here is that the user will go nuts.  We will have a
prominent entry in the docs: "using both nested transactions and
savepoints inside a transaction can cause confusion.  We recommend you
stick to one or the other."  Or something like that.

(What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
nested transactions created after the SAVEPOINT will be closed.)

So this is another reason why we should use COMMIT to close a nested
transaction: it may refer to a transaction that is already closed
because the user got confused.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)


Fri, 9 Jul 2004 11:47:51 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All
On Fri, 2004-07-09 at 11:45, 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.

Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere
you can ROLLBACK to, yes - exactly what I'm saying. 

I've not introduced any concept of "intermediate commit"...

Do you agree that my example is valid Oracle SQL? 

Best Regards, Simon Riggs




Fri, 09 Jul 2004 19:41:42 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Nested Transactions, Abort All
On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote:
> On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:
> 
> > 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
> 
> What happens here is that the user will go nuts.  We will have a
> prominent entry in the docs: "using both nested transactions and
> savepoints inside a transaction can cause confusion.  We recommend you
> stick to one or the other."  Or something like that.
> 
> (What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
> nested transactions created after the SAVEPOINT will be closed.)
> 
> So this is another reason why we should use COMMIT to close a nested
> transaction: it may refer to a transaction that is already closed
> because the user got confused.

Agreed.

Could we put two modes of operation in?
i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not
allowed to use nested transactions (and vice versa - so they are
mutually exclusive)...

Best Regards, Simon Riggs


Fri, 09 Jul 2004 20:03:36 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Nested Transactions, Abort All
Alvaro Herrera wrote:

>On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:
>
>  
>
>>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
>>    
>>
>
>What happens here is that the user will go nuts.  We will have a
>prominent entry in the docs: "using both nested transactions and
>savepoints inside a transaction can cause confusion.  We recommend you
>stick to one or the other."  Or something like that.
>
>(What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
>nested transactions created after the SAVEPOINT will be closed.)
>
>So this is another reason why we should use COMMIT to close a nested
>transaction: it may refer to a transaction that is already closed
>because the user got confused.
>
>  
>
Technically, a ROLLBACK TO SAVE POINT X would be an ABORT on all nested 
transactions.  COMMIT means that if the parent transaction commits then 
the child transaction will also commit.

BEGIN
  BEGIN
    UPDATE A
  ROLLBACK
  UPDATE B
COMMIT

The changes from UPDATE A will not commit with the changes from UPDATE B.

BEGIN
  BEGIN
    UPDATE A
  COMMIT
  UPDATE B
COMMIT

The changes from UPDATE A will commit with the changes from UPDATE B.

BEGIN
  BEGIN
    UPDATE A
  COMMIT
  UPDATE B
ROLLBACK

The changes from UPDATE A will not commit with the changes from UPDATE 
B, and the changes from UPDATE B will not commit either.


Fri, 09 Jul 2004 16:05:09 -0500
Thomas Swan <tswan@idigx.com>
Re: Nested Transactions, Abort All
Simon Riggs wrote:

>>>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.
>>    
>>
>
>Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere
>you can ROLLBACK to, yes - exactly what I'm saying. 
>
>I've not introduced any concept of "intermediate commit"...
>
>Do you agree that my example is valid Oracle SQL? 
>  
>
If you name that SAVEPOINT statements, yes. But the ordering of 
statements makes the second savepoint useless, because it's after all 
datamodifying statements; the first three are not covered by a savepoint 
at all, only the toplevel xaction. That's probably not what you wanted.

Regards,
Andreas

Sat, 10 Jul 2004 09:34:26 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Nested Transactions, Abort All
On Fri, Jul 09, 2004 at 08:03:36PM +0100, Simon Riggs wrote:
> On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote:

> > So this is another reason why we should use COMMIT to close a nested
> > transaction: it may refer to a transaction that is already closed
> > because the user got confused.

Sorry!  I wanted to say that we SHOULDN'T use "commit" to close a nested
transaction.  Rather we want to use a different command just so the
confusion does not close the outer transaction, which would not be what
the user wanted to do.

> Could we put two modes of operation in?
> i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not
> allowed to use nested transactions (and vice versa - so they are
> mutually exclusive)...

This may be a good idea.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Always assume the user will do much worse than the stupidest thing
you can imagine."                                (Julien PUYDT)


Sat, 10 Jul 2004 15:23:12 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Nested Transactions, Abort All