Let's start Pgpool-II 3.8 development!

We have started the Pgpool-II 3.8 development cycle.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=fb831287a2ed40505cb9a37136d1f0d0c5d5b22f

New items for 3.8 are summarized in the  Pgpool-II development wiki page.

In the wiki page, pinkish items are possible new features for 3.8. Discussions are in pgpool-hackers mailing list as usual.

There are some interesting features including SCRAM authentication in Pgpool-II. So please join the discussion!

pgpool-II 3.3 alpha 1 is out!

We are going to release new version of pgpool-II 3.3 soon. Previous version 3.2 was released in last August. Since then over 12,000 copies are downloaded. 3.2 was very successfull, since 3.1 was only downloaded only 5,000 in almost same period. So we expect 3.3 to be even more successfull than 3.2. So what's new in 3.2 anyway?

Well 3.3 focuses on maturing 3.2: especially "watchdog" part. The watchdog was new in 3.2, allows to prevent a single point of failure of pgpool itself. I know we already have pgpool-HA, but the watchdog does more. It can make more than two pgpool's working together, for example, on line recovery.

In 3.3 the watchdog is much more enhanced:

• New life check method (“heart beat”) added
• Redundant heart beat device support
• Secure protocol for heart beat and other messages
• Interlocking of fail over/fail back/follow maser script
• Monitor and auto restart watchdog process if it goes down

So please come and try 3.3 alpha now!

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.

pgpool-II and Hot Standby

PostgreSQL 9.0 will have a great feature called "Hot Standby". Unlike warm standby, which is just waiting for in case of master server goes down, it gives an access to the standby server for lots of useful purposes such as complex reporting query. However only read queries and other several commands are allowed to be sent to the standby server. According to the PostgreSQL 9.0 beta2 document, this is the list of allowed queries:

• Query access - SELECT, COPY TO including views and SELECT rules
• Cursor commands - DECLARE, FETCH, CLOSE
• Parameters - SHOW, SET, RESET
• Transaction management commands
• BEGIN, END, ABORT, START TRANSACTION
• SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
• EXCEPTION blocks and other internal subtransactions
• LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE.
• Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD
• Plugins and extensions - LOAD

No problem for PostgreSQL experts of course. But I doubt it is super easy for ordinary PostgreSQL users to know which query is allowed and which is not just looking at the list.

pgpool-II 3.0 (yes, that will be the new version number) will give you small help. Pgpool parses the query and automatically redirect it to the standby server if needed. If you have more than one standby servers, pgpool will choose one of them and send the query to it. The queries not allowed for the standby server, will be sent to the primary server. In short, you do not need to worry about the complex rule above if you use pgpool-II 3.0 + Hot Standby.

Please note that there is a limitation here. Pgpool parses the query by using "raw parser" which understands the syntax of the query but does not understand the "depth psychology" aspect of the query. For example, if a SELECT calls a custom C function which modifies the database, pgpool will send it to the standby server because it does not know that the SELECT actually does write.

It will be great that PostgeSQL's function does have a signature that confess it is going to write...