Re: Nested Transactions, Abort All

People,

Are we perhaps getting away from the issues here? The reason for this
discussion was to determine the user-level syntax for Alvaro's nested
transactions. We can discuss all we want about how he should have maybe
implemented some things differently, but we're supposed to start beta-testing
in 5 days and the current tangentalism of this discussion is unlikely to
produce such a result.

What we really have to determine is one of 4 options regarding the syntax for
Alvaro's patch:

1) We adopt one of the two PostgreSQL-specific syntaxes suggested by Alvaro,
based on SUBBEGIN or BEGIN NESTED. This would probably be the easiest
solution, but adds inconsistency with both the standard and other databases.

2) We adopt the syntax of the other databases to really push Nested
Transactions (as opposed to Savepoints), namely MSSQL and SyBase. This would
aid thousands of DBAs wishing to migrate to PostgreSQL, but would also mean
adopting a logically inconsistent syntax which is even further from the
standard than Alvaro's proposal.

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.

4) We hold back this patch until the next version. There is some merit in
this, due to the lack of consensus on functionality and Alvaro's
dissapointing discovery that we will not be able to use savepoints in
functions until next version. However, it would also mean effectively
dropping a major feature from 7.5 pretty much because we can't make up our
minds, and because nobody gave Alvaro adequate feedback when it was more
timely.

If you couldn't tell, I favor option 3) b) This syntax would look like:

BEGIN TRANSACTION; --begin main
do stuff;
SAVEPOINT; -- begin "nested transaction 1"
do more stuff;
SAVEPOINT; -- begin "nested transaction 2" inside "NT 1"
do stuff;
RELEASE SAVEPOINT; -- "commit" NT 2
do some more stuff;
test conditions: if bad:
ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process
if good:
RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2
do some more stuff
tests: if problem:
ROLLBACK; -- rollback entire transaction, including NT1 and NT2;
if good:
COMMIT; -- commit entire transaction, including NT1 and/or NT2
if they were good, excluding them if they were rolled back

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

If I'm not mistaken, the above matches the functionality already coded by
Alvaro. It begins but does not complete our compliance with SQL3 Savepoint
syntax, putting us on the right road but making developers aware that there
are some differences between our implementation and the standard. Thus
developers would be able to adopt the current syntax now, and the same
applications would still run when we complete standards-compliant syntax
later.

HOWEVER, I do still find one major flaw in Alvaro's implementation that I
can't seem to get other people on this list to take seriously, or maybe I'm
just not understanding the answers. One-half the point of Savepoints/Nested
Transactions is the ability to recover from certain kinds of errors (like
duplicate keys) inside a transaction and still commit the transaction after
the abort condition has been rolled back.
But the ability to detect an abort state *from the SQL command line* (or a
database port connection) has not been addressed. I've seen some comments
about functions to find an abort state from libpq in the text, but I'm not
even clear if this has been coded or is just theoretical. Parsing the
output of STDERR is *not* adequate. We need to be able to query whether we
are in an abort state, or we make NTs absolutely useless to any client
application that has connections which cannot, or do not yet, incorporate new
libpq functions, something which could take considerable time after the 7.5
release.
Do we already have an ability to query the SQLSTATE from the command line?
If so, what numbers indicate an abort state, if any?
Without this issue being addressed, I will change my opinion and vote for
option (4) because clearly the NT patch will not be ready for prime-time.

--
Josh Berkus
Aglio Database Solutions
San Francisco