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