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

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