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

[Sean Hogan]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130911/9498de5e/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sean.vcf
Type: text/x-vcard
Size: 275 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130911/9498de5e/attachment.vcf>