Monday, October 29, 2012

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.

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.