pgpool-II + now()

While  I attended PostgreSQL Conference Europe 2012 I noticed some one said "pgpool cannot handle  now()". Of course this is not a correct statement. Pgpool-II can handle now() since 2009 (version 2.3) in any mode.

In master/slave mode(external replication tool such as Slony-I and Streaming replication is responsible for duplicating database), now() is correctly handled as long as those replication tools work correctly.

In replication mode, pgpool-II is responsible for replication. Since pgpool-II is a statement based replication tool,  all DML/DCL statement are sent to all PostgreSQL servers. If this applies to statements using now(), this would be a problem.

Actually pgpool-II solves the  problem by using "query rewriting technique". Consider following INSERT statement:

INSERT INTO t1 VALUES(1, now());

Pgpool-II issues "SELECT now()" to master server to retrieve the timestamp constant. Then rewrite the INSERT by using the constant. Actual rewritten query will be something like this:

INSERT INTO "t1" VALUES (1,"pg_catalog"."timestamptz"('2012-10-30 09:35:43.910192+09'::text));

Therefore each PostgreSQL servers use exactly same timestamp value.

What if now() is using as the table default value? I mean:

CREATE TABLE t1 (int i, timestamp t default now());

and "INSERT INTO t1 VALUES(1)" issued? pgpool-II is smart enough to realize that  now() is used as a default value and complements it:

 INSERT INTO "t1" VALUES (1,'2012-10-30 09:51:22.880077+09');

Note that not only now() but other time/date functions including CURRENT_TIMESTAMP, CURRENT_DATE and CURRENT_TIME are processed like now() as well.

BTW, in the conference I also heard that "random() cannot be handled by pgpool-II". This is true. I think we could handle random() exactly same way as now(). Question is, if it's worth the trouble. If there's enough demand to handle random(), pgpool-II developers will gladly attack the problem.