Re: Transaction aborts on syntax error.

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.

--
greg