Обсуждение: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction
Bugs item #1000476, was opened at 2005-12-08 15:43 You can respond by visiting: http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000476&group_id=1000125 Category: None Group: None Status: Open Resolution: None Priority: 3 Submitted By: Nobody (None) Assigned to: Nobody (None) Summary: DateStyle and Transaction Initial Comment: When I work with auto_commit mode off, I can issue ODBC rollback command after my first statements. After that driver loseshis DateStyle ISO setting which one driver setup at begin. So, later it will return date fields in wrong format if my default database style is different then ISO (I'm using European,SQLformat). Here is example attached. Gzipped TAR archive includes mylog file and postgresql log file. I'm using Linux with unixODBC 2.2.9, psqlodbc 08.01.0102 with options "Threading=0, UseDeclareFetch=1". In application, I'veturned auto_commit mode off. P.S. The same thing we'll see if we cancel first select statement. After cancellation there will be "ROLLBACK" command whichdrops DateStyle setting too. What I think, driver should issue "COMMIT" right after settings in connection procedure.For example, call CC_commit() at the end of CC_send_settings() function. ---------------------------------------------------------------------- You can respond by visiting: http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000476&group_id=1000125
> In application, I've turned auto_commit mode off. > > P.S. The same thing we'll see if we cancel first select statement. > After cancellation there will be "ROLLBACK" command which drops > DateStyle setting too. What I think, driver should issue "COMMIT" > right after settings in connection procedure. For example, > call CC_commit() at the end of CC_send_settings() function. I thinks it's better to commit later in other place (e.g. commit set client_encoding too). Patch attached. Thanks for report and initial hint Luf
Вложения
Hi. Probably, you're right about CC_commit() call place, but I think it doesn't need to be checked about auto_commit mode. Because, after cancellation ROLLBACK will break DateStyle either in auto_commit mode on. I've used auto_commit mode off only to show how manual ROLLBACK will lose DateStyle. Regards. -- Eugene Fokin SOLVO Ltd. -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ludek Finstrle Sent: Friday, December 09, 2005 5:28 AM To: pgsql-odbc@postgresql.org Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction > In application, I've turned auto_commit mode off. > > P.S. The same thing we'll see if we cancel first select statement. > After cancellation there will be "ROLLBACK" command which drops > DateStyle setting too. What I think, driver should issue "COMMIT" > right after settings in connection procedure. For example, > call CC_commit() at the end of CC_send_settings() function. I thinks it's better to commit later in other place (e.g. commit set client_encoding too). Patch attached. Thanks for report and initial hint Luf
> Probably, you're right about CC_commit() call place, but I think it > doesn't need to be checked about auto_commit mode. Because, after > cancellation ROLLBACK will break DateStyle either in auto_commit mode > on. It's strenge. There is no begin transaction in autocommit mode on. I'll try it. Thanks Luf
I saw in the PostgreSQL log file, either in auto_commit mode on, psqlodbc generates implicit transaction for each "data change statement" and implicit transaction for whole bunch of consequent "select statmenets". So, it starts transaction at begin and doesn't close it before first "data change statement". If I'll cancel one of first select, I'll lose DateStyle. Regards. -- Eugene Fokin SOLVO Ltd. -----Original Message----- From: Ludek Finstrle [mailto:luf@pzkagis.cz] Sent: Friday, December 09, 2005 1:56 PM To: Eugene Fokin Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction > Probably, you're right about CC_commit() call place, but I think it > doesn't need to be checked about auto_commit mode. Because, after > cancellation ROLLBACK will break DateStyle either in auto_commit mode > on. It's strenge. There is no begin transaction in autocommit mode on. I'll try it. Thanks Luf
> I saw in the PostgreSQL log file, either in auto_commit mode on, > psqlodbc generates implicit transaction for each "data change statement" > and implicit transaction for whole bunch of consequent "select > statmenets". It's strange. Isn't it some misunderstanding? I try it here now and I see no start transaction in PgSQL log file. I have PgSQL 8.1 on Win32 and psqlodbc 8.01.0105 (I think it doesn't matter which 8.1 version). What versions do you have? > So, it starts transaction at begin and doesn't close it before first > "data change statement". If I'll cancel one of first select, I'll lose > DateStyle. My PgSQL log: 2005-12-18 22:02:30 LOG: autovacuum: processing database "postgres" 2005-12-18 22:03:05 LOG: statement: select version() 2005-12-18 22:03:05 LOG: statement: set DateStyle to 'ISO' 2005-12-18 22:03:05 LOG: statement: set geqo to 'OFF' 2005-12-18 22:03:05 LOG: statement: set extra_float_digits to 2 2005-12-18 22:03:05 LOG: statement: select oid from pg_type where typname='lo' 2005-12-18 22:03:06 LOG: statement: select pg_client_encoding() 2005-12-18 22:03:06 LOG: statement: set client_encoding to 'WIN1250' 2005-12-18 22:03:18 LOG: statement: select nothing from notable 2005-12-18 22:03:18 ERROR: relation "notable" does not exist 2005-12-18 22:03:27 LOG: statement: rollback 2005-12-18 22:03:27 WARNING: there is no transaction in progress Regards, Luf
I have PostgreSQL version 7.4.5. Also, I have "UseDeclareFetch=1" option. Try to turn it on, and I think you'll see BEGIN before "select version()". Regards. -- Eugene Fokin SOLVO Ltd. -----Original Message----- From: Ludek Finstrle [mailto:luf@pzkagis.cz] Sent: Sunday, December 18, 2005 11:09 PM To: Eugene Fokin Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction > I saw in the PostgreSQL log file, either in auto_commit mode on, > psqlodbc generates implicit transaction for each "data change statement" > and implicit transaction for whole bunch of consequent "select > statmenets". It's strange. Isn't it some misunderstanding? I try it here now and I see no start transaction in PgSQL log file. I have PgSQL 8.1 on Win32 and psqlodbc 8.01.0105 (I think it doesn't matter which 8.1 version). What versions do you have? > So, it starts transaction at begin and doesn't close it before first > "data change statement". If I'll cancel one of first select, I'll lose > DateStyle. My PgSQL log: 2005-12-18 22:02:30 LOG: autovacuum: processing database "postgres" 2005-12-18 22:03:05 LOG: statement: select version() 2005-12-18 22:03:05 LOG: statement: set DateStyle to 'ISO' 2005-12-18 22:03:05 LOG: statement: set geqo to 'OFF' 2005-12-18 22:03:05 LOG: statement: set extra_float_digits to 2 2005-12-18 22:03:05 LOG: statement: select oid from pg_type where typname='lo' 2005-12-18 22:03:06 LOG: statement: select pg_client_encoding() 2005-12-18 22:03:06 LOG: statement: set client_encoding to 'WIN1250' 2005-12-18 22:03:18 LOG: statement: select nothing from notable 2005-12-18 22:03:18 ERROR: relation "notable" does not exist 2005-12-18 22:03:27 LOG: statement: rollback 2005-12-18 22:03:27 WARNING: there is no transaction in progress Regards, Luf
> I have PostgreSQL version 7.4.5. > Also, I have "UseDeclareFetch=1" option. > Try to turn it on, and I think you'll see BEGIN before "select > version()". I turn UseDeclareFetch on and I see nothing wrong in mylog: 2005-12-19 22:00:07 LOG: autovacuum: processing database "postgres" 2005-12-19 22:00:23 LOG: statement: BEGIN 2005-12-19 22:00:23 LOG: statement: declare SQL_CUR00CD19A0 cursor for select version() 2005-12-19 22:00:24 LOG: statement: fetch 10 in SQL_CUR00CD19A0 2005-12-19 22:00:24 LOG: statement: close SQL_CUR00CD19A0 2005-12-19 22:00:24 LOG: statement: COMMIT 2005-12-19 22:00:24 LOG: statement: set DateStyle to 'ISO' 2005-12-19 22:00:24 LOG: statement: set geqo to 'OFF' 2005-12-19 22:00:24 LOG: statement: set extra_float_digits to 2 2005-12-19 22:00:24 LOG: statement: BEGIN 2005-12-19 22:00:24 LOG: statement: declare SQL_CUR00CD10E0 cursor for select oid from pg_type where typname='lo' 2005-12-19 22:00:24 LOG: statement: fetch 10 in SQL_CUR00CD10E0 2005-12-19 22:00:24 LOG: statement: close SQL_CUR00CD10E0 2005-12-19 22:00:24 LOG: statement: COMMIT 2005-12-19 22:00:24 LOG: statement: select pg_client_encoding() 2005-12-19 22:00:24 LOG: statement: set client_encoding to 'WIN1250' 2005-12-19 22:00:47 LOG: statement: BEGIN 2005-12-19 22:00:47 LOG: statement: declare SQL_CUR00CD10E0 cursor for select nothing from notable 2005-12-19 22:00:47 ERROR: relation "notable" does not exist 2005-12-19 22:00:47 LOG: statement: ROLLBACK Are you sure you use autocommit? Please could you post your mylog and pgsql log? Regards, Luf
Probably, this driver version is working correctly already. There was no such "COMMIT" in my 08.01.0102 version. I'll try to check last driver version against all my bugs. Thanks. -- Eugene Fokin SOLVO Ltd. -----Original Message----- From: Ludek Finstrle [mailto:luf@pzkagis.cz] Sent: Monday, December 19, 2005 11:05 PM To: Eugene Fokin Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction > I have PostgreSQL version 7.4.5. > Also, I have "UseDeclareFetch=1" option. > Try to turn it on, and I think you'll see BEGIN before "select > version()". I turn UseDeclareFetch on and I see nothing wrong in mylog: 2005-12-19 22:00:07 LOG: autovacuum: processing database "postgres" 2005-12-19 22:00:23 LOG: statement: BEGIN 2005-12-19 22:00:23 LOG: statement: declare SQL_CUR00CD19A0 cursor for select version() 2005-12-19 22:00:24 LOG: statement: fetch 10 in SQL_CUR00CD19A0 2005-12-19 22:00:24 LOG: statement: close SQL_CUR00CD19A0 >>> 2005-12-19 22:00:24 LOG: statement: COMMIT 2005-12-19 22:00:24 LOG: statement: set DateStyle to 'ISO' 2005-12-19 22:00:24 LOG: statement: set geqo to 'OFF' 2005-12-19 22:00:24 LOG: statement: set extra_float_digits to 2 2005-12-19 22:00:24 LOG: statement: BEGIN 2005-12-19 22:00:24 LOG: statement: declare SQL_CUR00CD10E0 cursor for select oid from pg_type where typname='lo' 2005-12-19 22:00:24 LOG: statement: fetch 10 in SQL_CUR00CD10E0 2005-12-19 22:00:24 LOG: statement: close SQL_CUR00CD10E0 2005-12-19 22:00:24 LOG: statement: COMMIT 2005-12-19 22:00:24 LOG: statement: select pg_client_encoding() 2005-12-19 22:00:24 LOG: statement: set client_encoding to 'WIN1250' 2005-12-19 22:00:47 LOG: statement: BEGIN 2005-12-19 22:00:47 LOG: statement: declare SQL_CUR00CD10E0 cursor for select nothing from notable 2005-12-19 22:00:47 ERROR: relation "notable" does not exist 2005-12-19 22:00:47 LOG: statement: ROLLBACK Are you sure you use autocommit? Please could you post your mylog and pgsql log? Regards, Luf