Re: Transaction aborts on syntax error.

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

Yeah, but in other db's this is solved by the frontend. e.g. in Informix
dbaccess has a mode that simply stops execution upon first error. So I don't
think this is a nogo argument, if we added such a feature to psql.

Imagine your script continuing with "insert into newtab ..." after the commit,
wouldn't you actually want that to not run eighter ?

Andreas

On Thu, Feb 12, 2004 at 09:55:36AM +0100, Zeugswetter Andreas SB SD wrote:
>
> Yeah, but in other db's this is solved by the frontend. e.g. in Informix
> dbaccess has a mode that simply stops execution upon first error. So I don't
> think this is a nogo argument, if we added such a feature to psql.

It does require that the application be meticulous in its checking though.
Existing client programs, for instance, may ignore any errors coming back
from PQexec() during the transaction and just see if the COMMIT succeeds.
Such could would break in very nasty ways with this change.

Jeroen

Zeugswetter Andreas SB SD wrote:
>
> >> 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?
>
> Yeah, but in other db's this is solved by the frontend. e.g. in Informix
> dbaccess has a mode that simply stops execution upon first error. So I don't
> think this is a nogo argument, if we added such a feature to psql.

Stops execution on the first error? What does that mean? It means it
stops reading the rest of the command file? We might be able to do
that (invalidate the entire session), but is that desired?

> Imagine your script continuing with "insert into newtab ..." after the commit,
> wouldn't you actually want that to not run eighter ?

Oh, yea, that would be bad. So you want to invalidate the entire
session on any error? That could be done.

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

"Jeroen T. Vermeulen" <jtv(at)xs4all(dot)nl> writes:
> It does require that the application be meticulous in its checking though.
> Existing client programs, for instance, may ignore any errors coming back
> from PQexec() during the transaction and just see if the COMMIT succeeds.
> Such could would break in very nasty ways with this change.

I think it's a given that the *default* behavior will not change.
You'll have to do something --- at least set a SET variable --- to get
intratransaction error recovery to behave differently. The risk of
breaking existing clients in subtle ways is too great if we do
otherwise.

regards, tom lane

> Oh, yea, that would be bad. So you want to invalidate the entire
> session on any error? That could be done.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001

Well, that's exactly the current behaviour, which creates certain
problems during interactive or programatic operation. Tom Lane,
described an excellent compromise to the problem, using "nested
transactions". libpg should be configurable to automatically start a
nested transaction for each statement within a transaction allowing
the outer transaction to continue in case of error. The error would
be communicated to the client in the normal manner.

This would not be the default since existing applications rely on the
entire transaction aborting.

-ESR-

>Bruce Momjian wrote
> Zeugswetter Andreas SB SD wrote:
> >
> > >> 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?
> >
> > Yeah, but in other db's this is solved by the frontend. e.g. in
Informix
> > dbaccess has a mode that simply stops execution upon first error. So
I
> don't
> > think this is a nogo argument, if we added such a feature to psql.
>
> Stops execution on the first error? What does that mean? It means it
> stops reading the rest of the command file? We might be able to do
> that (invalidate the entire session), but is that desired?

I wouldn't want to stop execution on the first error. All of that
transaction stuff doesn't apply to batch execution of script files - the
script is being executed blind, so having a decision point mid-way thru
a transaction isn't that important. This thread wasn't originally about
psql behaviour, though if we divert in that direction....I could see a
use for:

\if error then quit "newtab create failed"
or something like that

or even:
\retry on (with default=off)
to automatically submit an SQL statement if it fails with a retryable
error

Regards, Simon Riggs