Re: Transaction aborts on syntax error.

Greg Stark wrote:
> 
> Bruce Momjian  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@candle.pha.pa.us               |  (610) 359-1001 +  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073