Re: Nested Transactions, Abort All

Josh Berkus wrote:
> Bruce,
> 
> > Well, Oracle doesn't suppor RELEASE, so we are matching the standard but
> > perhaps not allowing easy migration from Oracle.
> 
> Well, that's Oracle's problem.  Considering the amount of influence they had 
> over the standard, there's no excuse for their syntax.     Also, if someone 
> converts and Oracle script which does not do  RELEASE, it's still ok with us; 
> they just end up nesting multiple levels and not "releasing" until the main 
> transaction is committed.

OK.

> > Don't we see the error from libpq PQexec() return value and other
> > interfaces?  
> 
> As far as I know, DBD::pg does not at this time; it detects an error but does 
> not return the SQLSTATE, and I'm *sure* that PHP 4 does not.  I'm sure there 
> are other interfaces in the same boat.   And nobody has answered the question 
> of what SQLSTATE ranges indicate an abort state as opposed to something else 
> -- I get the feeling that this is not at all defined.

They have no way of reporting a failed query back to the user?  How do
people program in those environments?  Right now any failed query aborts
the transaction so it seems it would be pretty easy.

> > Are you saying how do we detect a failure from a psql
> > script?
> 
> Right.  There are applications out there:  shell scripts, ODBC applications, 
> etc., which are unlikely to *ever* have the ability to read states from 
> libpq.  These applications need to have the ability to detect an abort *by 
> query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue 
> the proper ROLLBACKs.

Well, that involves either creating a conditional capability in the
backend, or in psql, neither of which will happen for 7.5.  The best we
can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE
ROLLBACK) and just let the script keep going. I am thinking of cases
where you want to drop an object you aren't sure exists in a
transaction.  Anything more complicated like issuing a replacement query
will have to wait for 7.6.

--  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