Josh Berkus wrote: > 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
Well, Oracle doesn't suppor RELEASE, so we are matching the standard but perhaps not allowing easy migration from Oracle. > 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. Don't we see the error from libpq PQexec() return value and other interfaces? Are you saying how do we detect a failure from a psql script? -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
