Re: Transaction aborts on syntax error.

Simon Riggs wrote:
> >Tom Lane
> > "Simon Riggs" <simon@2ndquadrant.com> writes:
> > > Most importantly, other references I have state that: the ANSI
> SQL-99
> > > specification does require that if a statement errors then only that
> > > statement's changes are rolled back.
> 
> > > ...if anybody has a copy of the actual spec could they check on
> this, so > > we can at least document carefully the current behaviour.
> 
> > 
> > No.  The spec says
> > 
> >          The execution of a <rollback statement> may be initiated
> > implicitly by an SQL-implementation when it detects unrecoverable
> errors.
> > 
> > and leaves it up to the implementation to define what is
> "unrecoverable".
> > Currently Postgres treats all errors as "unrecoverable".  This is
> > certainly not ideal, but it is within the letter of the spec.
> 
> Thanks for checking back to the spec, it's the only way.
> 
> Improving on "not ideal" would be good, and would get even closer to
> full Oracle/SQLServer migration/compatibility. However, since I've never
> looked at that section of code, I couldn't comment on any particular
> approach nor implement such a change, so I'll shut up and be patient.

Imagine this:
BEGIN WORK;LOCK oldtab;CREATE_X TABLE newtab AS SELECT * FROM oldtab;DELETE oldtab;COMMIT

In this case, you would want the database to abort on a syntax error, right?

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