[Pgpool-general] Validating and killing idle connections

[Tatsuo Ishii]

> I have a problem with a firewall which sits between my java web apps and
> postgresql. That firewall kills idle postgresql connections each hour, so if
> my web app is inactive for an hour, when the app tries to reconnect, it
> receives an exception, because the jdbc application does not know that the
> connection have been killed.
> 
> The problem is well known and has been documented. There are solutions for
> ir for some java application pools (Example for DBCP:
> http://sacharya.com/grails-dbcp-stale-connections/). The basis of the
> solution is that the connection pool itself closes idle connections after 30
> minutes, and validates connections before using them.
> 
> However, we have an app which uses one non standard connection pool, which
> will be very hard to change.
> 
> So I'm evaluating pgpool-ii, to act as a proxy between the java apps and
> postgresql. The configuration will be like this:
> 
> Java apps (localhost) -> pgpool-ii (localhost) -> Only 1 Remote posgresql
> Server
> 
> My question is how pgpool-ii works with the remote posgresql server. Are
> there some settings for closing and validating connections between pgpool-ii
> and postgresql? Or is pgpool-ii capable to retry queries when the connection
> is dropped, and without making the java apps failing?

Yes. If connection pool from pgpool-II to PostgreSQL is dropped by
someone, pgpool-II should validate it and try to reconnect. Also
pgpool-II has similar functionality as DBCP called
"connection_life_time". If you set it to other than 0, say 300,
pgpool-II will close the connection between pgpool-II and PostgreSQL,
which has been idle for 5 minutes. In this case pgpool-II will
reconnect if necessary as well.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp