Thread: 7.1 beta 3 Linux ODBC BEGIN Behaviour
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? Below are snipets from the psqlodbc log to show the change. I actually found the problem because our code does a number of queries against the database, and then issues a VACUUM ANALYZE. This stopped working with the 7.1 beta 3. Thanks, Steve <lines removed> conn=136030528, query='COMMIT' conn=136030528, query='BEGIN' conn=136030528, query='select count(*) from Objects' [ fetched 1 rows ] conn=136030528, query='select count(*) from ExchangeQueue ' [ fetched 1 rows ] conn=136030528, query='vacuum analyze' ERROR from backend during send_query: 'ERROR: VACUUM cannot run inside a BEGIN/END block' And, here's the same code executed under 7.0.3: conn=136031776, query='COMMIT' conn=136031776, query='select count(*) from Objects' [ fetched 1 rows ] conn=136031776, query='select count(*) from ExchangeQueue ' [ fetched 1 rows ] conn=136031776, query='vacuum analyze' ** Steve Wranovsky (stevew@merge.com) ** Merge Technologies Inc ** Lead Software Engineer ** Milwaukee, WI - USA ** Merge is a supplier of connectivity ** Phone: +1 (414) 977-4133 ** solutions for medical imaging applications ** http://www.merge.com
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. Regards, Hiroshi Inoue
I wrote: > > Steve Wranovsky wrote: > > > > > > 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. > I've just committed a change. However I couldn't test it sufficiently in my environment. Please try the current snapshot. Regards, Hiroshi Inoue
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. ===== Hiroki Kataoka
> 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? I think it can be. (We assumed this behavior a year ago.) Sometimes we need transaction time in the beginning of a transaction. We can get it with a SELECT. Because the transaction can begin only with an update statement, we must do a workaround - until now. > I actually found the problem because our code does a number > of queries against the database, and then issues a > VACUUM ANALYZE. This stopped working with the 7.1 beta 3. What the SQL specs say? Could this behavior an option in the driver? Regards, Tibor -- Tibor Laszlo ltibor@mail.tiszanet.hu
> -----Original Message-----
> From: LászlETibor
> Sent: Friday, February 09, 2001 8:03 PM
> To: pgsql-interfaces@postgresql.org
> Subject: RE: [INTERFACES] 7.1 beta 3 Linux ODBC BEGIN Behaviour
>
>
> > 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?
>
> I think it can be. (We assumed this behavior a year ago.) Sometimes we
> need transaction time in the beginning of a transaction. We can
> get it with
> a
> SELECT. Because the transaction can begin only with an update statement,
> we must do a workaround - until now.
>
Hmm, seems I committed a change too hastely.
Hiroki Kataoka also has a similar opinion.
The following statement types are in psqlodbc.h.
Well how about "begin" before TYPE_SELECT
etc and "commit" before TYPE_OTHER
statements ?
/* statement types */
enum {
STMT_TYPE_UNKNOWN = -2
STMT_TYPE_OTHER = -1,
STMT_TYPE_SELECT = 0,
STMT_TYPE_INSERT,
STMT_TYPE_UPDATE,
STMT_TYPE_DELETE,
STMT_TYPE_CREATE,
STMT_TYPE_ALTER,
STMT_TYPE_DROP,
STMT_TYPE_GRANT,
STMT_TYPE_REVOKE,
};
Regards,
Hiroshi Inoue
I would think you when a standard SELECT is issued, you would not want to
have a BEGIN, however, when a SELECT FOR UPDATE is issued, you may want
to issue the BEGIN in this case.
Is it easy to discriminate between these types of selects to decide when to
do the begin?
Regards,
Steve
At 11:38 PM 2/9/01 +0900, Hiroshi Inoue wrote:
>> -----Original Message-----
>> From: LászlETibor
>> Sent: Friday, February 09, 2001 8:03 PM
>> To: pgsql-interfaces@postgresql.org
>> Subject: RE: [INTERFACES] 7.1 beta 3 Linux ODBC BEGIN Behaviour
>>
>>
>> > 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?
>>
>> I think it can be. (We assumed this behavior a year ago.) Sometimes we
>> need transaction time in the beginning of a transaction. We can
>> get it with
>> a
>> SELECT. Because the transaction can begin only with an update statement,
>> we must do a workaround - until now.
>>
>
>Hmm, seems I committed a change too hastely.
>Hiroki Kataoka also has a similar opinion.
>
>The following statement types are in psqlodbc.h.
>Well how about "begin" before TYPE_SELECT
>etc and "commit" before TYPE_OTHER
>statements ?
>
>/* statement types */
>enum {
> STMT_TYPE_UNKNOWN = -2
> STMT_TYPE_OTHER = -1,
> STMT_TYPE_SELECT = 0,
> STMT_TYPE_INSERT,
> STMT_TYPE_UPDATE,
> STMT_TYPE_DELETE,
> STMT_TYPE_CREATE,
> STMT_TYPE_ALTER,
> STMT_TYPE_DROP,
> STMT_TYPE_GRANT,
> STMT_TYPE_REVOKE,
>};
>
>Regards,
>Hiroshi Inoue
> -----Original Message----- > From: Steve Wranovsky [mailto:stevew@merge.com] > > I would think you when a standard SELECT is issued, you would not want to > have a BEGIN, however, when a SELECT FOR UPDATE is issued, you may want > to issue the BEGIN in this case. > > Is it easy to discriminate between these types of selects to > decide when to > do the begin? > Unfortunately no(at least for me). The simplest solution is to simply issue BEGIN for all statements in autocommit off mode if transaction isn't in progress. However there are some commands(VACUUM etc) that couldn't be executed inside transaction blocks. Regards, Hiroshi Inoue
> -----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
> -----Original Message-----
> From: Tom Lane
>
> 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.
>
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 ?
Regards,
Hiroshi Inoue
At 06:37 AM 2/11/01 +0900, Hiroshi Inoue wrote: >> -----Original Message----- >> From: Tom Lane >> >> 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. >> > >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 don't think it is safe to have the driver issue a COMMIT for the user, mainly because it could end up hiding programming mistakes in that the user has failed to issue a COMMIT, or even a ROLLBACK in their code. Steve
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