Re: Nested Transactions, Abort All - Mailing list pgsql-hackers

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, orb) 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 maindo 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 NT2do some more stufftests: if problem:    ROLLBACK;
--rollback entire transaction, including NT1 and NT2;if good:    COMMIT; -- commit entire transaction, including NT1
and/orNT2             if they were good, excluding them if they were rolled back
 

In other words:SAVEPOINT == BEGIN NESTEDRELEASE SAVEPOINT == COMMIT NESTEDROLLBACK 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
votefor 
 
option (4) because clearly the NT patch will not be ready for prime-time.    
-- 
Josh Berkus
Aglio Database Solutions
San Francisco