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

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