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

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 placeso  doesn't work well with other xact synonyms like
BEGIN/ENDor   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;INSERTINTO ...;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,
Pennsylvania19073