pgpool-II 3.0 officially released

After nearly one month testing, we finally sent out pgpool-II 3.0 to the door on September 10, 2010. On that day SRA OSS, Inc. celebrated its 5th Anniversary. We had several talks there including Masao Fujii, the author of Streaming replication of PostgreSQL 9.0. Another talk was given by Toshihiro Kitagwa, the major developer of pgpool-II, explaining how Streaming replication/Hot standby can be work with pgpool-II.
About one hundred people joined the session. I was very happy to report "pgpool-II is out today" to them.

The details of pgpool-II 3.0 can be found at: http://lists.pgfoundry.org/pipermail/pgpool-general/2010-September/002946.html

From now on I would like to explain new features of pgpool-II 3.0, probably one feature per one blog entry.

Today's topic is the "white function list" and "black function list".

Background: pgpool-II needs to know the usage of functions in SELECT statements which will write to database. If such SELECT is given, pgpool-II should not do load balance. Instead it sends the SELECT to al DB nodes(in replication mode) or sends to the master node (in master/slave mode).

Typical queries are:

SELECT nextval('foo_sequence');
SELECT setval('foo_sequence', 100);

In previous version pgpool-II found nextval and setval in hard coded way. Problem is, user might want to create their own functions which does write. The work around was to use SQL comment. For example:

/*NO LOAD BALANCE*/ SELECT my_writing_function();

This is not only unpolished but forcing users to rewrite SQL statements in existing applications. In pgpool-II 3.0, you can use black function list in such cases:

black_function_list = 'nextval,setval,my_writing_function'

White_function_list works in exactly opposite way. Any functions not listed in the white_function_list are regarded as being write to database.

Pgpool-II 3.0 also enhances the way to search functions in SELECT. Before it only searchs in the top level of SELECT. That is, if those functions are used in subquery or some such, it cannot detect the existence of the functions. Pgpool-II 3.0 does a recursive search against he parse tree and can find those functions anywhere.