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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
