Re: Nested Transactions, Abort All

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