Re: 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

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