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

[Tatsuo Ishii]

Sean,

Good catch! Thanks. Will fix.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Tatsuo,
> 
> The bug is on line 315 of pool_timestamp.c.  The timestamp[] array is
> one character too short.
> 
> Sean
> 
> 
> On 13-09-11 08:49 AM, Sean Hogan wrote:
>> Thanks for the suggestion about statement logging.  I think it holds
>> the answer:
>>
>> LOG:  statement: create table t1  (tm timestamp with time zone);
>> LOG:  statement: COMMIT
>> LOG:  statement: BEGIN
>> LOG: statement: SELECT count(*) from (SELECT
>> has_function_privilege('postgres', 'pgpool_regclass(cstring)',
>> 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE
>> p.proname = 'pgpool_regclass')) AS s
>> LOG: statement: SELECT count(*) FROM pg_catalog.pg_attrdef AS d,
>> pg_catalog.pg_class AS c WHERE d.adrelid = c.oid AND d.adsrc ~
>> 'nextval' AND c.oid = pgpool_regclass('t1')
>> LOG: statement: SELECT attname, d.adsrc, coalesce((d.adsrc LIKE
>> '%now()%' OR d.adsrc LIKE '%''now''::text%') AND (a.atttypid =
>> 'timestamp'::regtype::oid OR a.atttypid = 'timestamp with time
>> zone'::regtype::oid OR a.atttypid = 'date'::regtype::oid OR a.atttypid
>> = 'time'::regtype::oid OR a.atttypid = 'time with time
>> zone'::regtype::oid) , false) FROM pg_catalog.pg_class c,
>> pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef d ON
>> (a.attrelid = d.adrelid AND a.attnum = d.adnum) WHERE c.oid =
>> a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid =
>> pgpool_regclass('t1') ORDER BY a.attnum
>> LOG:  statement: SELECT now()
>> LOG: statement: INSERT INTO "t1"("tm") VALUES
>> ("pg_catalog"."timestamptz"('2013-09-11 08:44:23.004694-02:3'::text))
>> LOG:  statement: COMMIT
>>
>> Notice that the timezone on the second last statement is '-02:3'. I
>> looked more closely and the inserted time is actually 27 minutes off,
>> not 30.  That is consistent with a timezone '-02:03'.
>>
>> Sean
>>
>>
>> On 13-09-10 10:59 PM, Tatsuo Ishii wrote:
>>> 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
>>
>>
>>
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>