Re: Transaction aborts on syntax error.

Greg Stark wrote:
>
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>
> > 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?
>
> Certainly not if I was typing this from the command line. Imagine the
> frustration if the typo was in "DELETE oldtab" and the create statement took
> hours.
>
> I would want the application to receive the error in a clean API that provides
> an option to automatically initiate a rollback whenever the client receives an
> error.
>
> In an application I would expect the database layer to provide a clean API to
> catch the error. Preferably one making it hard to avoid aborting the
> transaction and rolling back except intentionally. The best interface in most
> languages is to throw an exception. In any case it's up to the application to
> decide how to handle the error.
>
> Tom's explanation of the implementation issues makes perfect sense. Though I
> do wonder whether it would be possible to detect certain degenerate cases of
> queries that haven't caused any database changes at all before they errored
> out.
>
> This wouldn't help if you do a "delete" that causes an error after deleting a
> few thousand records, but it would catch the low hanging fruits of syntax
> errors.

I suppose we could have a SET that psql could set when it was
interactive and skip rollback on syntax errors, but that is pretty
exotic. Also consider that other errors could abort a query aside from
syntax errors, like deadlocks.

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