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

[Sean Hogan]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: sean.vcf
Type: text/x-vcard
Size: 275 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20130911/31dc2e4d/attachment.vcf>