Обсуждение: RE: [INTERFACES] 7.1 beta 3 Linux ODBC BEGIN Behaviour

> -----Original Message-----
> From: Hiroki Kataoka
>
> Hiroshi Inoue wrote
> >
> > Steve Wranovsky wrote:
> > >
> > > Hello,
> > >
> > > I found another slight behavior change with the Linux
> > > client ODBC library between 7.0.3 and the 7.1 beta 3
> > > release.  We are running under Redhat 6.0.
> > >
> > > We have set the ODBC driver in autocommit off mode.  With
> > > the 7.0.3 driver, a BEGIN was automatically issued
> > > before the driver encountered a INSERT/UPDATE/DELETE.  With
> > > the 7.1 beta 3 ODBC driver, it now also issues a BEGIN when it
> > > encounters a SELECT.
> > >
> > > Is this the expected behavior under 7.1?
> >
> > Probably not.
> > I would put back the behabior in a few days.
>
>   There must be "SELECT ~ FOR UPDATE" of inside of the transaction.
>

You are right.
However psqlodbc has never checked "for update".
My recent change doesn't take "for update" into
account either.
You know psqlodbc much more than me.
Could you make a patch for it ?
Or could you suggest how to do it ?

Regards,
Hiroshi Inoue

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> There must be "SELECT ~ FOR UPDATE" of inside of the transaction.

> You are right.
> However psqlodbc has never checked "for update".
> My recent change doesn't take "for update" into
> account either.

It'd be nice if ODBC could distinguish SELECT FOR UPDATE from plain
SELECT, but in practice it cannot reliably do so.  Doubtless we could
extend ODBC to look for "FOR UPDATE" in the text of the query, but
that will only catch simple situations.  Consider these possibilities:

* A view or rule invoked by the query uses FOR UPDATE.  (Pre-7.1, we
didn't support FOR UPDATE in views ... but we do now.)

* A function invoked by the query does SELECT FOR UPDATE internally.

For that matter, it's quite possible for a function invoked by a SELECT
to do INSERT/UPDATE/DELETE internally.  Therefore, it's impossible for
the ODBC driver to reliably distinguish a pure SELECT from a SELECT that
causes locking or even data updates.

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.

            regards, tom lane

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 ?

Regards,
Hiroshi Inoue