Bugs item #1000476, was opened at 2005-12-08 15:43
You can respond by visiting:
http://pgfoundry.org/tracker/?func=d...oup_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 loses his 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,SQL format).
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'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.

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=d...oup_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> 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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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
[mailtogsql-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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly