Re: Transaction aborts on syntax error.

Simon Riggs wrote:
> >Tom Lane
> > "Simon Riggs" <simon(at)2ndquadrant(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073