[pgpool-general: 187] Timestamp functions rewriting problem

[Bartosz Rebeś]

First of all, as a new subscriber I’d like to say hello to everyone.

I’m working on storage replication for a cloud management platform. I’m using pgpool in replication mode with the following setup: one pgpool 3.1 and two PostgreSQL 8.4.9 nodes. Apparently, there’s a problem with rewriting  timestamp functions when  schema it explicitly specified.
Normally when inserting CURRENT_TIMESTAMP to the table, it gets correctly rewritten:

# CREATE TABLE foo_bar (date timestamp);
CREATE TABLE
# INSERT INTO foo_bar VALUES (NOW());
INSERT 0 1

Now, when I execute select on both nodes, dates are the same, as expected:
Node 0:
# SELECT * FROM foo_bar;
            date            
----------------------------
 2012-01-20 15:13:48.614674
(1 row)

Node 1:
# SELECT * FROM foo_bar;
            date            
----------------------------
 2012-01-20 15:13:48.614674
(1 row)

However, when explicitly accessing a table in some custom schema, rewriting doesn’t work anymore:
# CREATE SCHEMA foo
CREATE SCHEMA
# CREATE TABLE foo.bar (date timestamp);
CREATE TABLE
# INSERT INTO foo.bar VALUES (NOW());
INSERT 0 1

This time timestamps are different:
Node 0:
# SELECT * FROM foo.bar;
            date            
----------------------------
 2012-01-20 15:12:24.906677
(1 row)

Node 1:
# SELECT * FROM foo.bar;
           date           
--------------------------
 2012-01-20 15:12:24.9059
(1 row)

It is possible to work around it with setting SEARCH_PATH, but this would require the whole system we’re using to be rewritten.
Can you help, please?


-- 
Regards,
Bartosz Rebeś

Developer at DynaCloud
E-mail: b.rebes at dynacloud.com
Mobile.: +31 647 111 631

DynaCloud 
Web: www.dynacloud.com
Email: info at dynacloud.com
Tel.: +31 (0) 538 200 921