[pgpool-general: 188] Re: Timestamp functions rewriting problem

[Tatsuo Ishii]

> 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?

Have you installed pgpool_regclass? To deal with schema qualified
table name(like 'foo.bar'), you need to install the function into your
database.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp