Thread: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

[ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
Date:
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

Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
Ludek Finstrle
Date:
> 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

Attachment

Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
"Eugene Fokin"
Date:
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


Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
Ludek Finstrle
Date:
> 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

Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
"Eugene Fokin"
Date:
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


Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
Ludek Finstrle
Date:
> 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

Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
"Eugene Fokin"
Date:
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


Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
Ludek Finstrle
Date:
> 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

Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction

From
"Eugene Fokin"
Date:
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