Re: Nested Transactions, Abort All

Josh Berkus wrote:

> 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would
> have the twin benefit of both allowing us to improve our standards compliance
> and make savepoints completely compliant in the next version, as well as
> helping those wishing to migrate from Oracle to PostgreSQL (currently our
> largest source of migrations). Its disadvantage is the subtle differences
> between Alvaro's patch and the standard, which might not be obvious to users
> and lead to difficult to locate errors. This option also comes in two
> flavors:
> a) we implement savepoint names, troubleshooting the namespace and scoping
> issues, which would really make this a different feature and delay beta
> testing, or
> b) we do anonymous savepoints for now, which more-or-less exactly matches the
> current behavior of Alvaro's patch, and do complaint, named savepoints in the
> next version.

As Dennis has said, whatever we do now we should support "for ever". If
we end up with compliant SAVEPOINT (eventually in 7.6+) plus some
nonstandard syntax (from 7.5), what is the nonstandard syntax you would
prefer to see? I'd prefer a syntax that reflects the primitives actually
in use i.e. BEGIN NESTED.

[...]

> In other words:
> SAVEPOINT == BEGIN NESTED
> RELEASE SAVEPOINT == COMMIT NESTED
> ROLLBACK TO SAVEPOINT == ROLLBACK NESTED

As pointed out by others ROLLBACK TO SAVEPOINT is actually ROLLBACK
NESTED + BEGIN NESTED. This means that if we only have savepoint syntax,
there is no way to do a plain rollback of a nested transaction (you have
to ROLLBACK TO SAVEPOINT foo; RELEASE SAVEPOINT foo which I find pretty
ugly and nonobvious, and it gives you an extra empty subtransaction)

> But the ability to detect an abort state *from the SQL command line* (or a
> database port connection) has not been addressed.

There is a transaction state indicator in the V3 protocol's
ReadyForQuery message. One of the states is "in aborted transaction".
It's been around since 7.4.

Whatever logic is needed for running different/conditional SQL based on
transaction state then belongs on the client side, IMO.

-O