[pgpool-general: 2117] Re: possible timezone handling issue

Tatsuo Ishii ishii at postgresql.org
Wed Sep 11 10:29:34 JST 2013


BTW, I assume that you are using pgpool's native replication mode.
Also I am using PostgreSQL 9.3.0. If you could provide PostgreSQL's
statement log, maybe it'd be useful to understand what's going on.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Did not reproduce here (although in different time zone).
> 
> test=# show pool_version;
>     pool_version     
> ---------------------
>  3.3.1 (tokakiboshi)
> (1 row)
> 
> test=# create table t1(tm timestamp with time zone);
> CREATE TABLE
> test=# insert into t1 (tm) values (current_timestamp);
> INSERT 0 1
> test=# select * from t1;
>               tm               
> -------------------------------
>  2013-09-11 09:54:39.828622+09
> (1 row)
> 
> [t-ishii at localhost aaa]$ LANG=C date
> Wed Sep 11 09:55:02 JST 2013
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> Hi,
>> 
>> I happen to live in a 30 minute time zone (GMT-2:30 at the
>> moment). Today I got a user report complaining that when they insert
>> current_timestamp into a "timestamp with time zone" column, the column
>> ends up containing a time that is 30 minutes too early (but with the
>> correct timezone).
>> 
>> $ psql -h psql-vip -U postgres
>> postgres=# create table t1 (tm timestamp with time zone);
>> CREATE TABLE
>> postgres=# insert into t1 (tm) values (current_timestamp);
>> INSERT 0 1
>> postgres=# select * from t1;
>>                 tm
>> ----------------------------------
>>  2013-09-10 13:41:05.648345-02:30
>> (1 row)
>> 
>> $ date
>> Tue Sep 10 14:08:13 NDT 2013
>> 
>> If I do the exact same test against the real PostgreSQL backend, the
>> column contains the correct time:
>> 
>> $ psql -h psql-vm1 -p 5433 -U postgres
>> postgres=# create table t2 (tm timestamp with time zone);
>> CREATE TABLE
>> postgres=# insert into t2 (tm) values (current_timestamp);
>> INSERT 0 1
>> postgres=# select * from t2;
>>                 tm
>> ----------------------------------
>>  2013-09-10 14:15:34.205086-02:30
>> (1 row)
>> 
>> $ date
>> Tue Sep 10 14:15:39 NDT 2013
>> 
>> 
>> Does pgpool-II have a limitation in this area?  It's an enormous
>> problem for me because our application's scheduler records activity
>> times this way.
>> 
>> Thanks,
>> Sean
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list