Обсуждение: PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hi All,
I am trying to certify our product with PostgreSQL database and facing an issue with windows odbc driver and need your help to identify the problem.
Our application uses SQLDriverConnect to connect to PostgreSQL server. I am using PostgreSQL odbc driver version 9.4
SQLSetConnectAttr to enable autocommit as below:
Eg: SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, 0)
But it seems autocommit is not set and hence any queries executed from other session never gets updated data in other session since autocommit of insert statements never happens.
Only workaround I am seeing is to explicitly issue “commit” to save the updates in the database.
Has anyone seen this issue earlier? I see a relevant issue in psqlODBC 09.05.0100 Release something like below:
https://odbc.postgresql.org/docs/release.html
1. Don't reset autocommit when a connection is established
If autocommit is disabled on a connection, by calling SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0), before connecting with SQLDriverConnect(), autocommit was incorrectly reset back to on when the connection was established.
Is the above issue fixed? Also in my use-case, I want to enable autocommit at odbc driver level but even that doesn’t work.
The same application works in Linux OS when unix odbc driver is used. This seems to be specific to windows driver.
Please shed some light on this. Do ask me further question if you have any.
Thanks,
Sekhar
On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > Is the above issue fixed? Also in my use-case, I want to enable > autocommit at odbc driver level but even that doesn’t work. > > The same application works in Linux OS when unix odbc driver is used. > This seems to be specific to windows driver. > > Please shed some light on this. Do ask me further question if you have any. > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committing it. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development.
HI Joshua, I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0) ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. Can you please confirm on this behavior? Thanks, Sekhar -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Thursday, January 28, 2016 11:14 PM To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > Is the above issue fixed? Also in my use-case, I want to enable > autocommit at odbc driver level but even that doesn't work. > > The same application works in Linux OS when unix odbc driver is used. > This seems to be specific to windows driver. > > Please shed some light on this. Do ask me further question if you have any. > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committing it. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development.
Including few people in this loop. -----Original Message----- From: Venkatesan, Sekhar Sent: Friday, January 29, 2016 10:04 AM To: 'Joshua D. Drake'; 'pgsql-odbc@postgresql.org' Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! HI Joshua, I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0) ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. Can you please confirm on this behavior? Thanks, Sekhar -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Thursday, January 28, 2016 11:14 PM To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > Is the above issue fixed? Also in my use-case, I want to enable > autocommit at odbc driver level but even that doesn't work. > > The same application works in Linux OS when unix odbc driver is used. > This seems to be specific to windows driver. > > Please shed some light on this. Do ask me further question if you have any. > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committing it. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development.
On 01/28/2016 08:36 PM, Venkatesan, Sekhar wrote: > HI Joshua, > > I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. > SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0) > > ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); > > This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. > Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. > Can you please confirm on this behavior? I would say it is this bug: http://www.postgresql.org/message-id/55BD0ABE.5010800@iki.fi So have you tried the latest psqlodbc version, 09.05.0100 ? > > Thanks, > Sekhar > > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Thursday, January 28, 2016 11:14 PM > To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! > > On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > >> Is the above issue fixed? Also in my use-case, I want to enable >> autocommit at odbc driver level but even that doesn't work. >> >> The same application works in Linux OS when unix odbc driver is used. >> This seems to be specific to windows driver. >> >> Please shed some light on this. Do ask me further question if you have any. >> > > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committingit. > > Sincerely, > > JD > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 2016/01/29 19:25, Venkatesan, Sekhar wrote: > Including few people in this loop. > > -----Original Message----- > From: Venkatesan, Sekhar > Sent: Friday, January 29, 2016 10:04 AM > To: 'Joshua D. Drake'; 'pgsql-odbc@postgresql.org' > Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! > > HI Joshua, > > I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. > SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0) > > ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); > > This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. > Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. > Can you please confirm on this behavior? > Unfortunately I can't reproduce it. Could you take the Mylog output of the case? regards, Hiroshi Inoue
Hi Adrian, Sorry for getting back to you late. I tried with psqlodbc version, 09.05.0100 but the issue is still seen? Let me know what next step I can follow to root cause this? Any tracing you would suggest? Thanks, Sekhar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Saturday, January 30, 2016 3:00 AM To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! On 01/28/2016 08:36 PM, Venkatesan, Sekhar wrote: > HI Joshua, > > I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. > SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) > SQL_AUTOCOMMIT_ON, 0) > > ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), > SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); > > This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. > Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. > Can you please confirm on this behavior? I would say it is this bug: http://www.postgresql.org/message-id/55BD0ABE.5010800@iki.fi So have you tried the latest psqlodbc version, 09.05.0100 ? > > Thanks, > Sekhar > > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Thursday, January 28, 2016 11:14 PM > To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! > > On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > >> Is the above issue fixed? Also in my use-case, I want to enable >> autocommit at odbc driver level but even that doesn't work. >> >> The same application works in Linux OS when unix odbc driver is used. >> This seems to be specific to windows driver. >> >> Please shed some light on this. Do ask me further question if you have any. >> > > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committingit. > > Sincerely, > > JD > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 02/08/2016 08:10 PM, Venkatesan, Sekhar wrote: Ccing list. > Hi Adrian, > > Sorry for getting back to you late. I tried with psqlodbc version, 09.05.0100 but the issue is still seen? > Let me know what next step I can follow to root cause this? Any tracing you would suggest? I do not have an answer, returning this to list for those that might. > > Thanks, > Sekhar > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Saturday, January 30, 2016 3:00 AM > To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! > > On 01/28/2016 08:36 PM, Venkatesan, Sekhar wrote: >> HI Joshua, >> >> I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. >> SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) >> SQL_AUTOCOMMIT_ON, 0) >> >> ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), >> SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); >> >> This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. >> Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. >> Can you please confirm on this behavior? > > I would say it is this bug: > > http://www.postgresql.org/message-id/55BD0ABE.5010800@iki.fi > > So have you tried the latest psqlodbc version, 09.05.0100 ? > >> >> Thanks, >> Sekhar >> >> -----Original Message----- >> From: Joshua D. Drake [mailto:jd@commandprompt.com] >> Sent: Thursday, January 28, 2016 11:14 PM >> To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org >> Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! >> >> On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: >> >>> Is the above issue fixed? Also in my use-case, I want to enable >>> autocommit at odbc driver level but even that doesn't work. >>> >>> The same application works in Linux OS when unix odbc driver is used. >>> This seems to be specific to windows driver. >>> >>> Please shed some light on this. Do ask me further question if you have any. >>> >> >> By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committingit. >> >> Sincerely, >> >> JD >> >> >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Can anyone shed some light on this issue (highlighted in BOLD )?
FYI, I have tried with psqlodbc driver version 9.5 but no luck.
Thanks,
Sekhar
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Tuesday, February 09, 2016 8:45 PM
To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
On 02/08/2016 08:10 PM, Venkatesan, Sekhar wrote:
Ccing list.
> Hi Adrian,
>
> Sorry for getting back to you late. I tried with psqlodbc version, 09.05.0100 but the issue is still seen?
> Let me know what next step I can follow to root cause this? Any tracing you would suggest?
I do not have an answer, returning this to list for those that might.
>
> Thanks,
> Sekhar
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Saturday, January 30, 2016 3:00 AM
> To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
>
> On 01/28/2016 08:36 PM, Venkatesan, Sekhar wrote:
>> HI Joshua,
>>
>> I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows.
>> SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)
>> SQL_AUTOCOMMIT_ON, 0)
>>
>> ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(),
>> SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
>>
>> This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set.
>> Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection.
>> Can you please confirm on this behavior?
>
> I would say it is this bug:
>
> http://www.postgresql.org/message-id/55BD0ABE.5010800@iki.fi
>
> So have you tried the latest psqlodbc version, 09.05.0100 ?
>
>>
>> Thanks,
>> Sekhar
>>
>> -----Original Message-----
>> From: Joshua D. Drake [mailto:jd@commandprompt.com]
>> Sent: Thursday, January 28, 2016 11:14 PM
>> To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org
>> Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
>>
>> On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote:
>>
>>> Is the above issue fixed? Also in my use-case, I want to enable
>>> autocommit at odbc driver level but even that doesn't work.
>>>
>>> The same application works in Linux OS when unix odbc driver is used.
>>> This seems to be specific to windows driver.
>>>
>>> Please shed some light on this. Do ask me further question if you have any.
>>>
>>
>> By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committing it.
>>
>> Sincerely,
>>
>> JD
>>
>>
>>
>>
>
>
> --
> Adrian Klaver
>
--
Adrian Klaver
On 02/15/2016 01:07 AM, Venkatesan, Sekhar wrote: > Can anyone shed some light on this issue (highlighted in *BOLD )?* > > FYI, I have tried with psqlodbc driver version 9.5 but no luck. Given that this is the first of two problems you have only on Windows, I would suspect your Windows setup. So: How are you installing psqlodbc on Windows? What version of Windows are you using? What exactly is your application(the C++ I presume) doing? Where is the application located, on Windows, on Linux, both? > > ** > > Thanks, > Sekhar > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, February 09, 2016 8:45 PM > To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver > doesnt work!!! > > On 02/08/2016 08:10 PM, Venkatesan, Sekhar wrote: -- Adrian Klaver adrian.klaver@aklaver.com
Re: PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hello, Sekhar, > Sorry for getting back to you late. I tried with psqlodbc version, 09.05.0100 > but the issue is still seen? > Let me know what next step I can follow to root cause this? Any tracing > you would suggest? Have you been able to solve this problem? I tried the attached test program with psqlodbc-09.05.0100 on both Linux and Windows. It worked as expected in both casesof SQL_AUTOCOMMIT_ON and SQL_AUTOCOMMIT_OFF. That is, I could see the inserted row from another (psql) session afterthe test program had finished. Could you share the source code of your sample program that doesn't work? BTW, just out of curiosity, what product are you trying to certify with PostgreSQL? I'm interested in expanding the PostgreSQLecosystem. Regards Takayuki Tsunakawa > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar > Sent: Tuesday, February 09, 2016 1:10 PM > To: Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver > doesnt work!!! > > Hi Adrian, > > Sorry for getting back to you late. I tried with psqlodbc version, 09.05.0100 > but the issue is still seen? > Let me know what next step I can follow to root cause this? Any tracing > you would suggest? > > Thanks, > Sekhar > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Saturday, January 30, 2016 3:00 AM > To: Venkatesan, Sekhar; Joshua D. Drake; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver > doesnt work!!! > > On 01/28/2016 08:36 PM, Venkatesan, Sekhar wrote: > > HI Joshua, > > > > I perform the following set of operations to set autocommit in postgreSQL > through ODBC in windows. > > SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) > > SQL_AUTOCOMMIT_ON, 0) > > > > ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), > > SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); > > > > This doesn't enable autocommit. If I call SQLSetConnectAttr after > SQLDriverConnect, autocommit is set. > > Any issue with this sequence? I assume It should be possible to set > SQL_ATTR_AUTOCOMMIT before establishing connection. > > Can you please confirm on this behavior? > > I would say it is this bug: > > http://www.postgresql.org/message-id/55BD0ABE.5010800@iki.fi > > So have you tried the latest psqlodbc version, 09.05.0100 ? > > > > > Thanks, > > Sekhar > > > > -----Original Message----- > > From: Joshua D. Drake [mailto:jd@commandprompt.com] > > Sent: Thursday, January 28, 2016 11:14 PM > > To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org > > Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver > doesnt work!!! > > > > On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > > > >> Is the above issue fixed? Also in my use-case, I want to enable > >> autocommit at odbc driver level but even that doesn't work. > >> > >> The same application works in Linux OS when unix odbc driver is used. > >> This seems to be specific to windows driver. > >> > >> Please shed some light on this. Do ask me further question if you have > any. > >> > > > > By default PostgreSQL "autocommits". If it isn't in your case, your driver > is starting a transaction and not committing it. > > > > Sincerely, > > > > JD > > > > > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > -- > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-odbc
Вложения
Re: PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Sorry, the attached test program is the right one.
Try adding the following line in postgresql.conf and run your program:
log_statement = 'all'
If SQL_AUTOCOMMIT_OFF is effective, these lines are output in the server log. This shows that the ODBC driver sent
BEGINat the first SQL statement in a transaction, and the application explicitly committed the transaction with
SQLEndTran(SQL_COMMIT).
LOG: statement: BEGIN;INSERT INTO a VALUES(100)
LOG: statement: COMMIT
If SQL_AUTOCOMMIT_ON is effective, this line is output. The ODBC driver doesn't send BEGIN.
LOG: statement: INSERT INTO a VALUES(100)
Regards
Takayuki Tsunakawa
Вложения
Hi Takayuki,
Thanks for your in-depth information. It helped a lot. AutoCommit worked the right way as you indicated in your test code.
There was an application problem where-in explicit transaction was opened and before the updates got committed in postgres database, we hit an error (application error) due to which entire transaction got rolled back.
Your helpful suggestion of enabling logging to identify behavior of autocommit helped me catch the exact problem.
Appreciate your help on this. Sincere thanks to everyone who replied to my email with valuable guidance J.
Thanks,
Sekhar
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Wednesday, February 17, 2016 9:14 AM
To: Venkatesan, Sekhar; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Sorry, the attached test program is the right one.
Try adding the following line in postgresql.conf and run your program:
log_statement = 'all'
If SQL_AUTOCOMMIT_OFF is effective, these lines are output in the server log. This shows that the ODBC driver sent BEGIN at the first SQL statement in a transaction, and the application explicitly committed the transaction with SQLEndTran(SQL_COMMIT).
LOG: statement: BEGIN;INSERT INTO a VALUES(100)
LOG: statement: COMMIT
If SQL_AUTOCOMMIT_ON is effective, this line is output. The ODBC driver doesn't send BEGIN.
LOG: statement: INSERT INTO a VALUES(100)
Regards
Takayuki Tsunakawa
Hi Takayuki,
One thing I wanted to point out is that I didn’t see this issue in Linux OS. The application code is the same for both windows and Linux. In both the cases, we are in a transaction and an error occurred.
In linux, inserts are getting committed. In Windows, inserts are not getting committed. I will fix the application code to resolve the error. But just want to know the reason for different behavior.
When I enabled tracing in both, I see this difference:
Windows (After insert statements in tables):
2016-02-17 16:47:10 GMT ERROR: relation "dm_job_request_s" does not exist at character 24
2016-02-17 16:47:10 GMT STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
2016-02-17 16:47:10 GMT LOG: statement: ROLLBACK
2016-02-17 16:47:10 GMT LOG: statement: BEGIN
Linux (After insert statements in tables):
< 2016-02-18 01:17:53.173 EST >ERROR: relation "dm_job_request_s" does not exist at character 24
< 2016-02-18 01:17:53.173 EST >STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
< 2016-02-18 01:17:53.173 EST >LOG: statement: ROLLBACK to _EXEC_SVP_0x35fa4b0
< 2016-02-18 01:17:53.173 EST >LOG: statement: RELEASE _EXEC_SVP_0x35fa4b0
In linux, there is one extra call to release the savepoint which is not the case in windows.
Is this causing the behavior change in windows when compared to linux?
What is the expected behavior when “Release” savepoint is called?
Note: In linux, we are talking to linux PostgreSQL server. In Windows, We are talking to windows PostgreSQL server.
Thanks,
Sekhar
From: Venkatesan, Sekhar
Sent: Thursday, February 18, 2016 9:58 AM
To: 'Tsunakawa, Takayuki'; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hi Takayuki,
Thanks for your in-depth information. It helped a lot. AutoCommit worked the right way as you indicated in your test code.
There was an application problem where-in explicit transaction was opened and before the updates got committed in postgres database, we hit an error (application error) due to which entire transaction got rolled back.
Your helpful suggestion of enabling logging to identify behavior of autocommit helped me catch the exact problem.
Appreciate your help on this. Sincere thanks to everyone who replied to my email with valuable guidance J.
Thanks,
Sekhar
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Wednesday, February 17, 2016 9:14 AM
To: Venkatesan, Sekhar; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Sorry, the attached test program is the right one.
Try adding the following line in postgresql.conf and run your program:
log_statement = 'all'
If SQL_AUTOCOMMIT_OFF is effective, these lines are output in the server log. This shows that the ODBC driver sent BEGIN at the first SQL statement in a transaction, and the application explicitly committed the transaction with SQLEndTran(SQL_COMMIT).
LOG: statement: BEGIN;INSERT INTO a VALUES(100)
LOG: statement: COMMIT
If SQL_AUTOCOMMIT_ON is effective, this line is output. The ODBC driver doesn't send BEGIN.
LOG: statement: INSERT INTO a VALUES(100)
Regards
Takayuki Tsunakawa
It's not the difference between Windows and Linux.
There is an option *Level of rollback on errors* to specify either nop, transaction or statement.
regards,
Hiroshi Inoue
Hi Takayuki,
One thing I wanted to point out is that I didn’t see this issue in Linux OS. The application code is the same for both windows and Linux. In both the cases, we are in a transaction and an error occurred.
In linux, inserts are getting committed. In Windows, inserts are not getting committed. I will fix the application code to resolve the error. But just want to know the reason for different behavior.
When I enabled tracing in both, I see this difference:
Windows (After insert statements in tables):
2016-02-17 16:47:10 GMT ERROR: relation "dm_job_request_s" does not exist at character 24
2016-02-17 16:47:10 GMT STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
2016-02-17 16:47:10 GMT LOG: statement: ROLLBACK
2016-02-17 16:47:10 GMT LOG: statement: BEGIN
Linux (After insert statements in tables):
< 2016-02-18 01:17:53.173 EST >ERROR: relation "dm_job_request_s" does not exist at character 24
< 2016-02-18 01:17:53.173 EST >STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
< 2016-02-18 01:17:53.173 EST >LOG: statement: ROLLBACK to _EXEC_SVP_0x35fa4b0
< 2016-02-18 01:17:53.173 EST >LOG: statement: RELEASE _EXEC_SVP_0x35fa4b0
In linux, there is one extra call to release the savepoint which is not the case in windows.
Is this causing the behavior change in windows when compared to linux?
What is the expected behavior when “Release” savepoint is called?
Note: In linux, we are talking to linux PostgreSQL server. In Windows, We are talking to windows PostgreSQL server.
Thanks,
Sekhar
Re: PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hello, Sekhar,
As Inoue-san mentioned, the difference of the behavior comes from the "Protocol" connection parameter setting. The parameter is described on the following page. Refer to "Level of rollback on errors". (The description is a bit obscure to me, as the parameter name is strange and the value format is not intuitive...)
https://odbc.postgresql.org/docs/config.html
My guess about your setting is:
* On Linux, you don't specify Protocol parameter either in odbc.ini nor in the connection string. So, the default behavior of Sentence(2) is chosen. That is, the failed SELECT statement was only rolled back and other statements including INSERT were committed.
* On Windows, you created a data source with some Windows tool (ODBC Administrator, ODBCCONF.EXE, or PowerShell command). The tool created a data source with the Protocol parameter set to "7.4-1". "-1" means Transaction(1), causing the entire transaction to roll back. Check the "Protocol" value in the following registry keys:
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<data source name>
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<data source name>
Regards
Takayuki Tsunakawa
From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]
Sent: Thursday, February 18, 2016 3:46 PM
To: Tsunakawa, Takayuki/綱川 貴之; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Cc: Rao, Raghavendra
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hi Takayuki,
One thing I wanted to point out is that I didn’t see this issue in Linux OS. The application code is the same for both windows and Linux. In both the cases, we are in a transaction and an error occurred.
In linux, inserts are getting committed. In Windows, inserts are not getting committed. I will fix the application code to resolve the error. But just want to know the reason for different behavior.
When I enabled tracing in both, I see this difference:
Windows (After insert statements in tables):
2016-02-17 16:47:10 GMT ERROR: relation "dm_job_request_s" does not exist at character 24
2016-02-17 16:47:10 GMT STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
2016-02-17 16:47:10 GMT LOG: statement: ROLLBACK
2016-02-17 16:47:10 GMT LOG: statement: BEGIN
Linux (After insert statements in tables):
< 2016-02-18 01:17:53.173 EST >ERROR: relation "dm_job_request_s" does not exist at character 24
< 2016-02-18 01:17:53.173 EST >STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
< 2016-02-18 01:17:53.173 EST >LOG: statement: ROLLBACK to _EXEC_SVP_0x35fa4b0
< 2016-02-18 01:17:53.173 EST >LOG: statement: RELEASE _EXEC_SVP_0x35fa4b0
In linux, there is one extra call to release the savepoint which is not the case in windows.
Is this causing the behavior change in windows when compared to linux?
What is the expected behavior when “Release” savepoint is called?
Note: In linux, we are talking to linux PostgreSQL server. In Windows, We are talking to windows PostgreSQL server.
Thanks,
Sekhar
You hit the nail on the head J
Thanks Takayuki and Hiroshi Inoue. The protocol parameter you mentioned is the cause for this issue.
In windows, it is set to “7.4-1” by default.
Appreciate all for the help throughout my investigation.
Thanks,
Sekhar
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Friday, February 19, 2016 8:37 AM
To: Venkatesan, Sekhar; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Cc: Rao, Raghavendra
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hello, Sekhar,
As Inoue-san mentioned, the difference of the behavior comes from the "Protocol" connection parameter setting. The parameter is described on the following page. Refer to "Level of rollback on errors". (The description is a bit obscure to me, as the parameter name is strange and the value format is not intuitive...)
https://odbc.postgresql.org/docs/config.html
My guess about your setting is:
* On Linux, you don't specify Protocol parameter either in odbc.ini nor in the connection string. So, the default behavior of Sentence(2) is chosen. That is, the failed SELECT statement was only rolled back and other statements including INSERT were committed.
* On Windows, you created a data source with some Windows tool (ODBC Administrator, ODBCCONF.EXE, or PowerShell command). The tool created a data source with the Protocol parameter set to "7.4-1". "-1" means Transaction(1), causing the entire transaction to roll back. Check the "Protocol" value in the following registry keys:
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<data source name>
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<data source name>
Regards
Takayuki Tsunakawa
From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]
Sent: Thursday, February 18, 2016 3:46 PM
To: Tsunakawa, Takayuki/綱川 貴之; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Cc: Rao, Raghavendra
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Hi Takayuki,
One thing I wanted to point out is that I didn’t see this issue in Linux OS. The application code is the same for both windows and Linux. In both the cases, we are in a transaction and an error occurred.
In linux, inserts are getting committed. In Windows, inserts are not getting committed. I will fix the application code to resolve the error. But just want to know the reason for different behavior.
When I enabled tracing in both, I see this difference:
Windows (After insert statements in tables):
2016-02-17 16:47:10 GMT ERROR: relation "dm_job_request_s" does not exist at character 24
2016-02-17 16:47:10 GMT STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
2016-02-17 16:47:10 GMT LOG: statement: ROLLBACK
2016-02-17 16:47:10 GMT LOG: statement: BEGIN
Linux (After insert statements in tables):
< 2016-02-18 01:17:53.173 EST >ERROR: relation "dm_job_request_s" does not exist at character 24
< 2016-02-18 01:17:53.173 EST >STATEMENT: SELECT 1 AS total FROM dm_job_request_s S, dm_job_request_r R WHERE S.r_object_id = R.r_object_id AND arguments_keys = 'OldUserName' and arguments_values = $1 AND request_completed = 0 AND job_name = 'dm_UserRename'
< 2016-02-18 01:17:53.173 EST >LOG: statement: ROLLBACK to _EXEC_SVP_0x35fa4b0
< 2016-02-18 01:17:53.173 EST >LOG: statement: RELEASE _EXEC_SVP_0x35fa4b0
In linux, there is one extra call to release the savepoint which is not the case in windows.
Is this causing the behavior change in windows when compared to linux?
What is the expected behavior when “Release” savepoint is called?
Note: In linux, we are talking to linux PostgreSQL server. In Windows, We are talking to windows PostgreSQL server.
Thanks,
Sekhar