Re: Nested Transactions, Abort All

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073