<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Tatsuo,<br>
<br>
The bug is on line 315 of pool_timestamp.c. The timestamp[] array
is one character too short.<br>
<br>
Sean<br>
<br>
<br>
On 13-09-11 08:49 AM, Sean Hogan wrote:<br>
</div>
<blockquote cite="mid:523051BD.4070705@compusult.net" type="cite">Thanks
for the suggestion about statement logging. I think it holds the
answer:
<br>
<br>
LOG: statement: create table t1 (tm timestamp with time zone);
<br>
LOG: statement: COMMIT
<br>
LOG: statement: BEGIN
<br>
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
<br>
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')
<br>
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
<br>
LOG: statement: SELECT now()
<br>
LOG: statement: INSERT INTO "t1"("tm") VALUES
("pg_catalog"."timestamptz"('2013-09-11
08:44:23.004694-02:3'::text))
<br>
LOG: statement: COMMIT
<br>
<br>
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'.
<br>
<br>
Sean
<br>
<br>
<br>
On 13-09-10 10:59 PM, Tatsuo Ishii wrote:
<br>
<blockquote type="cite">BTW, I assume that you are using pgpool's
native replication mode.
<br>
Also I am using PostgreSQL 9.3.0. If you could provide
PostgreSQL's
<br>
statement log, maybe it'd be useful to understand what's going
on.
<br>
--
<br>
Tatsuo Ishii
<br>
SRA OSS, Inc. Japan
<br>
English: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a>
<br>
Japanese: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
<br>
<br>
<blockquote type="cite">Did not reproduce here (although in
different time zone).
<br>
<br>
test=# show pool_version;
<br>
pool_version
<br>
---------------------
<br>
3.3.1 (tokakiboshi)
<br>
(1 row)
<br>
<br>
test=# create table t1(tm timestamp with time zone);
<br>
CREATE TABLE
<br>
test=# insert into t1 (tm) values (current_timestamp);
<br>
INSERT 0 1
<br>
test=# select * from t1;
<br>
tm
<br>
-------------------------------
<br>
2013-09-11 09:54:39.828622+09
<br>
(1 row)
<br>
<br>
[t-ishii@localhost aaa]$ LANG=C date
<br>
Wed Sep 11 09:55:02 JST 2013
<br>
--
<br>
Tatsuo Ishii
<br>
SRA OSS, Inc. Japan
<br>
English: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a>
<br>
Japanese: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
<br>
<br>
<blockquote type="cite">Hi,
<br>
<br>
I happen to live in a 30 minute time zone (GMT-2:30 at the
<br>
moment). Today I got a user report complaining that when
they insert
<br>
current_timestamp into a "timestamp with time zone" column,
the column
<br>
ends up containing a time that is 30 minutes too early (but
with the
<br>
correct timezone).
<br>
<br>
$ psql -h psql-vip -U postgres
<br>
postgres=# create table t1 (tm timestamp with time zone);
<br>
CREATE TABLE
<br>
postgres=# insert into t1 (tm) values (current_timestamp);
<br>
INSERT 0 1
<br>
postgres=# select * from t1;
<br>
tm
<br>
----------------------------------
<br>
2013-09-10 13:41:05.648345-02:30
<br>
(1 row)
<br>
<br>
$ date
<br>
Tue Sep 10 14:08:13 NDT 2013
<br>
<br>
If I do the exact same test against the real PostgreSQL
backend, the
<br>
column contains the correct time:
<br>
<br>
$ psql -h psql-vm1 -p 5433 -U postgres
<br>
postgres=# create table t2 (tm timestamp with time zone);
<br>
CREATE TABLE
<br>
postgres=# insert into t2 (tm) values (current_timestamp);
<br>
INSERT 0 1
<br>
postgres=# select * from t2;
<br>
tm
<br>
----------------------------------
<br>
2013-09-10 14:15:34.205086-02:30
<br>
(1 row)
<br>
<br>
$ date
<br>
Tue Sep 10 14:15:39 NDT 2013
<br>
<br>
<br>
Does pgpool-II have a limitation in this area? It's an
enormous
<br>
problem for me because our application's scheduler records
activity
<br>
times this way.
<br>
<br>
Thanks,
<br>
Sean
<br>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
<br>
</body>
</html>