[ 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=10004 76&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 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=detail&atid=538&aid=10004 76&group_id=1000125

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

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

--+HP7ph2BbKc20aGI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

> 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

--+HP7ph2BbKc20aGI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-autocommit_off.diff"

diff -c psqlodbc.orig\connection.c psqlodbc\connection.c
*** psqlodbc.orig\connection.c Sun Dec 04 22:16:28 2005
--- psqlodbc\connection.c Fri Dec 09 03:58:42 2005
***************
*** 1343,1348 ****
--- 1343,1351 ----
ci->updatable_cursors = ci->allow_keyset;
#endif /* DRIVER_CURSOR_IMPLEMENT */

+ if (!CC_is_in_autocommit(self))
+ CC_commit(self);
+
CC_clear_error(self); /* clear any initial command errors */
self->status = CONN_CONNECTED;


--+HP7ph2BbKc20aGI
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


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

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

--+HP7ph2BbKc20aGI--

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

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


---------------------------(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

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

Re: [ 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".

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

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

=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ludek Finstrle
> Sent: 10 December 2005 06:05
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and=20
> Transaction
>=20
> > You can respond by visiting:=20
> >=20
> http://pgfoundry.org/tracker/?func=3Ddetail&atid=3D538&aid=3 D1000476
> &group_id=3D1000125
> > Category: None
> > Group: None
> > >Status: Closed
>=20
> Are you sure Dave about close this bug? I don't think so.
> See this thread about the topic:
> http://archives.postgresql.org/pgsql-odbc/2005-12/msg00114.p hp

Ack sorry - that's what you get for trying to work late at night! I've
re-opened the tracker until we decide if the behaviour with the patch is
100% correct or not. At very least it's 50% better than before :-)

> Do you think it's good idea to do explicit begin, commit around
> connect settings all the time? I don't have time for test
> this behaviour yet.

Probably wouldn't be a bad idea - at the very least one tx with all the
hard-coded queries, then one for any user-supplied connect string. I'll
try to find some time to look in the next few days (pretty busy
releasing pgAdmin 1.4.1 and PostgreSQL 8.1.1/8.0.5 for Windows atm).

Regards, Dave

---------------------------(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

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

> > Do you think it's good idea to do explicit begin, commit around
> > connect settings all the time? I don't have time for test
> > this behaviour yet.
>
> Probably wouldn't be a bad idea - at the very least one tx with all the
> hard-coded queries, then one for any user-supplied connect string. I'll
> try to find some time to look in the next few days (pretty busy
> releasing pgAdmin 1.4.1 and PostgreSQL 8.1.1/8.0.5 for Windows atm).

You haven't to code it. I ask only for opinion from other developers.
I take a look on in after I study SQL[GS]etStmtAttr. I don't write
code today. I study ODBC specification because I want to clean up
the code.

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

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

=20

> -----Original Message-----
> From: Ludek Finstrle [mailto:luf@pzkagis.cz]=20
> Sent: 10 December 2005 17:16
> To: Dave Page
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] [ psqlodbc-Bugs-1000476 ] DateStyle and=20
> Transaction
>=20
> > > Do you think it's good idea to do explicit begin, commit around
> > > connect settings all the time? I don't have time for test
> > > this behaviour yet.
> >=20
> > Probably wouldn't be a bad idea - at the very least one tx=20
> with all the
> > hard-coded queries, then one for any user-supplied connect=20
> string. I'll
> > try to find some time to look in the next few days (pretty busy
> > releasing pgAdmin 1.4.1 and PostgreSQL 8.1.1/8.0.5 for Windows atm).
>=20
> You haven't to code it. I ask only for opinion from other developers.

Yeah, I realise that :-) I just like to look and experiment before
commenting. It wasn't that long ago that I only used and packaged
psqlODBC so I'm still not familiar enough to comment immediately in many
areas.

/D

---------------------------(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

Re: [ 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 5: don't forget to increase your free space map settings

Re: [ 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()".

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

Re: [ 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 2: Don't 'kill -9' the postmaster

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

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