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
