RE: [ODBC] RE: 7.1 beta 3 Linux ODBC BEGINBehaviour

> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue(at)tpf(dot)co(dot)jp]
> Sent: 13 February 2001 10:47
> To: Dave Page
> Cc: Steve Wranovsky; pgsql-interfaces(at)postgresql(dot)org;
> pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] RE: [INTERFACES] 7.1 beta 3 Linux ODBC
> BEGINBehaviour
>
>
> Dave Page wrote:
> >
> > >
> > > Steve Wranovsky wrote:
> > > >
> > > > >>
> > > > >> Given these considerations, I think it's a mistake for
> > > ODBC to treat
> > > > >> SELECT differently from other queries for the
> purpose of setting
> > > > >> transaction boundaries.
> > > > >>
> > > > >
> > > > >OK, agreed.
> > > > >However simply putting back the behabior make it
> impossible to call
> > > > >VACUUM in psqlodbc autocommit off mode.
> > > > >
> > > > >My idea is as follows.
> > > > > [In autocommit off mode]
> > > > > 1) All statements except STMT_TYPE_OTHER issue
> > > > > "BEGIN" if a trasaction isn't in progress.
> > > > > 2) STMT_TYPE_OTHER statements automatically issue
> > > > > "COMMIT" if a transaction is progress.
> > > > >
> > > > >Comments ?
> > > >
> > > > I now agree with point 1 above, but for point 2, I believe
> > > you should
> > > > force the user to issue a COMMIT if a transaction is in progress
> > > > when they try a VACUUM ANALYZE.
> > >
> > > I've been waiting for reply.
> > > I see. It's the simplest change. But you seem to have to
> > > change your existent your code. Or you may have to distinguish
> > > your code according to PG servers. Is it OK ?
> >
> > I've not really been following this thread, but changing
> code or writing
> > server version dependant code sounds very bad to me.
> Certainly in my case I
> > have enough issues as it is keeping pgAdmin working with
> the current and
> > previous release...
> >
>
> Sorry my explanation was wrong. The version is of driver not
> of server. I'm confirming him in his inconvenience.
>
> In the previous release(6.5.???) in autocommit off mode
> Steve's query sequence e.g.
> select ...
> vacuum ...
> was successful because neither "select" nor "vacuum"
> issued "BEGIN".
>
> In 7.01.0001
> select ...
> issues "BEGIN" and subsequent
> vacuum ...
> fails because "vacuum" isn't allowed to be called inside
> a transaction block. Even though he inserts
> commit
> after "select", "vacuum" itself issues "BEGIN" and fails.
>

Ahh, I see. I suffered this problem with pgAdmin - it's not just VACUUM that
is affected. CREATE/ALTER/DROP USER/DATABASE are also affected, and maybe
other queries as well. I did raise this some time ago and got no response
other than the ODBC driver (and other affected interfaces) should *not* be
changed to stop wrapping such queries in a transaction, rather the
restriction that was added to PostgreSQL 7.0 to throw the error should be
removed. I did exchange email with Thomas Lockhart who was keen to fix the
problem and considered it a 'release stopper'. I know that some causes of
the error message were removed, but don't know if he got them all.

Incidently, pgAdmin gets round the problem by prepending a space to affected
queries! It seems that postgresql will error with "VACUUM" but not "
VACUUM". Odd....

Regards,

Dave.