Bruce,

> > So, whatever "error handling mode" conveniences we wish to put in should
> > be put in on the client side.
> 
> Added to TODO:
> 
>         * Use nested transactions to prevent syntax errors from aborting
>           a transaction

Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are 
we defining a "syntax error"?

I write a lot of procedures for T-SQL with error-controlled rollback, and a 
few for Oracle.   I can tell you that all of the errors which I anticipate 
for, and thus do not abort the operation when I encounter, fall into one of 
these types:
1) Constraint conflict: duplicate key.
2) Constraint/Data Type conflict:  bad value format
3) Duplicate object name
4) Object not found
5) Lock conflict

Other types of errors, such as the syntax error raised by forgetting the 
"GROUP BY" are things that I *want* to be fatal and cause immediate rollback.  
In fact, one of issues I have on-and-off with SQL Server is that *nothing* is 
fatal by default except not being able to access the databse; as a result, 
one needs to manually check for an error after every statement.   You can 
imagine what happens if you forget one of those checks.

I don't want to go to this by default with postgresql; I still prefer the 
default abort transaction.   What would be a much easier integration, IMHO, 
is offering something like Perl's eval{ } that would allow for special 
rollback conditions in an application-defined block.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Fri, 13 Feb 2004 13:20:27 -0800
Josh Berkus <josh@agliodbs.com>
Re: Transaction aborts on syntax error.
Josh Berkus wrote:
> Bruce,
> 
> > > So, whatever "error handling mode" conveniences we wish to put in should
> > > be put in on the client side.
> > 
> > Added to TODO:
> > 
> > ????????* Use nested transactions to prevent syntax errors from aborting
> > ???????? ?a transaction
> 
> Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are 
> we defining a "syntax error"?

Parser error, I would say.  Misspelling a table name, perhaps.  Not sure
on that one, but clearly this opens a can of worms we will have to deal
with someday.

> I write a lot of procedures for T-SQL with error-controlled rollback, and a 
> few for Oracle.   I can tell you that all of the errors which I anticipate 
> for, and thus do not abort the operation when I encounter, fall into one of 
> these types:
> 1) Constraint conflict: duplicate key.
> 2) Constraint/Data Type conflict:  bad value format
> 3) Duplicate object name
> 4) Object not found
> 5) Lock conflict

Certainly this will never be the default.   My guess is that for these
cases, you will have to code the transaction/subtransaction yourself in
your 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, Pennsylvania 19073

Fri, 13 Feb 2004 20:39:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction aborts on syntax error.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Josh Berkus wrote:
>> Hmmm .... I'm not sure how you arrived at this wording for the TODO.  How are 
>> we defining a "syntax error"?
>
> Parser error, I would say.  Misspelling a table name, perhaps.

FWIW, a misspelled table name is plainly a semantic error, not a
syntactic one.

-Neil


Mon, 23 Feb 2004 12:40:44 -0500
Neil Conway <neilc@samurai.com>
Re: Transaction aborts on syntax error.