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

Josh Berkus wrote:
> Bruce,
> 
> > 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.
> 
> Believe it or not, PHP4 doesn't.   This is one of the reasons why coders in 
> other languages don't consider PHP a "real" programming language; the lack of 
> exception handling.   However, given this limitation we can't really use NTs 
> in PHP4 anyway, so it's sort of a moot point.  Sorry for bringing it up.
> 
> To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the 
> DBD::Pg driver reports back a query exception, but not the SQLSTATE.  This 
> means that we can detect an abort (assuming all exceptions are aborts) but 
> not what caused the abort, except by parsing the error message for text -- a 
> hazardous approach at best.  But you would be right to point out that this is 
> a problem with the DBD::Pg driver.
> 
> There are, however, other client applications where the problem is more 
> ingrained.   I've done an application for ColdFusion recently, and discovered 
> that CF is completely unable to detect even the limited error-reporting 
> capability of ODBC.   This means that if CF can't query it, it doesn't exist.

Well, I don't think we need exception handling to support failed
transactions.  Don't these function calls return some failure result
code?

> > 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.
> 
> OK, I didn't realize that it was a difficult thing.   I think it should go on 
> the TODO list but you are the judge of what's a quick fix and what's not.

Adding something to psql or the backend like IF (ERROR) ... would be a
big job, I would think.

> (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE 
> ERROR)

The syntax was for support of script languages that don't have
conditional constructs, like psql scripts, where you want the subxact to
commit but if it fails, you don't want that to affect the outer
transaction.  Are you saying there are very few cases where you don't
care if the subxact commits or aborts?

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