[Pgpool-general] Confused about JDBC and load balancing

[Tatsuo Ishii]

> Found the problem.  It's in the PostgreSQL "valid connection checker" class provided by JBoss.  A typical JBoss data source configuration file for PostgreSQL
> includes the following XML:
> 
>     <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.PostgreSQLValidConnectionChecker</valid-connection-checker-class-name>
> 
> The source code for the PostgreSQLValidConnectionChecker class includes the following validity check:
> 
>       try
>       {
>          stmt = cn.createStatement();
>          stmt.execute("");
>       }
>       // etc..
> 
> This empty-string query is exactly what's causing the problem.  When I disable the connection checker by commenting out the XML element shown above, everything works correctly.
> 
> Apparently, this JBoss behavior is intentional, for performance reasons that came from discussing with PostgreSQL developers.  See https://issues.jboss.org/browse/JBAS-3133 and http://community.jboss.org/message/298853#298853 for details.
> 
> At any rate, I *strongly* recommend including this information in the pgpool-II documentation.  The current version of JBoss (AS 7) still has this code (see https://source.jboss.org/browse/JBossAS/connector/src/main/java/org/jboss/as/connector/adapters/jdbc/extensions/postgres/PostgreSQLValidConnectionChecker.java?r=6c64725de60a324628dc648baae080a9a7516dca).

Thank you for the info. I will add up to FAQ about this (adding to
official docs may not be usefull since the info will be not be given
to the user who have same probalem as you until they upgrade
pgpool-II).

Of course we will look into the problem you pointed out in the mean time.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp