Re: PostgreSQL: Autocommit through windows odbc driver doesnt work!!!

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

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