Postgres idle connections: CannotAcquireResourceException

Question

I have a web application in Java/ Wicket using Hibernate 4.1.1 and Spring 3.1.1 and Postgres 9.2. I also use spring transaction api and c3p0 connection pool.

My Problem is that the available connections are not released and after executing some select statements caused by the gui I get the following Exception

com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source. Executing SELECT * FROM pg_stat_activity; shows that there are many idle queries like this : SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX'
WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW'
ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.ob

This is my applicationContext.xml:

 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:lang="http://www.springframework.org/schema/lang"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
                           http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
                           http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang-3.1.xsd"
       default-autowire="byName">


    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                    destroy-method="close">
           <property name="driverClass" value="org.postgresql.Driver"/>
           <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/mydatabase"/>
           <property name="user" value="myuser"/>
           <property name="password" value="mypassword"/>

           <!-- configuration pool via c3p0 -->
           <property name="initialPoolSize" value="3"/>
           <property name="minPoolSize" value="3"/>
           <property name="maxPoolSize" value="30"/>
           <property name="idleConnectionTestPeriod" value="600"/>
           <property name="maxIdleTime" value="0"/>
           <property name="maxStatements" value="0"/>
           <property name="maxStatementsPerConnection" value="0"/>
           <property name="acquireIncrement" value="1"/>
           <property name="acquireRetryAttempts" value="3"/>
           <property name="acquireRetryDelay" value="1000"/>
           <property name="autoCommitOnClose" value="false"/>
           <property name="maxConnectionAge" value="14400"/>
           <property name="forceIgnoreUnresolvedTransactions" value="false"/>
           <property name="numHelperThreads" value="20"/>
           <property name="testConnectionOnCheckin" value="false"/>
           <property name="testConnectionOnCheckout" value="false"/>
           <property name="maxAdministrativeTaskTime" value="0"/>
           <property name="debugUnreturnedConnectionStackTraces" value="false"/>
           <property name="maxIdleTimeExcessConnections" value="0"/>
           <property name="breakAfterAcquireFailure" value="false"/>
           <property name="checkoutTimeout" value="0"/>
           <property name="unreturnedConnectionTimeout" value="0"/>
           <property name="usesTraditionalReflectiveProxies" value="false"/>
      </bean>

    <bean id="exampleDao"
        class="com.mycompany.persistence.hibernate.daoimpl.ExampleDaoImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>


    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
      <property name="dataSource" ref="dataSource" />
      <property name="annotatedClasses">
        <list>
            <value>com.mycompany.model.Example</value>
        </list>
      </property>

      <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.format_sql">true</prop>
            <prop key="hibernate.current_session_context_class">org.springframework.orm.hibernate4.SpringSessionContext</prop>
            <prop key="hibernate.auto_close_session">true</prop>
            <prop key="hibernate.c3p0.maxSize">30</prop>
            <prop key="hibernate.c3p0.minSize">1</prop>
            <prop key="hibernate.c3p0.acquireIncrement">1</prop>
            <prop key="hibernate.c3p0.idleTestPeriod">300</prop>
            <prop key="hibernate.c3p0.timeout">1800</prop>
            <prop key="hibernate.c3p0.maxStatements">0</prop>
            <prop key="hibernate.c3p0.unreturnedConnectionTimeout">30</prop> 
            <prop key="hibernate.c3p0.debugUnreturnedConnectionStackTraces">true</prop>
            <prop key="hibernate.c3p0.checkoutTimeout">0</prop>
            <prop key="hibernate.c3p0.preferredTestQuery">SELECT * FROM dual</prop>
        </props>
      </property>
    </bean>

   <!-- Transaction Management -->
   <tx:annotation-driven transaction-manager="transactionManager"/>
    <bean id="transactionManager"
    class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory"/>
    </bean>
</beans>

Is the configuration correct?

My Classes to retrieve the data from the database are like this:

@Repository
public class ExampleDaoImpl extends BaseDaoImpl implements ExampleDao {
...
    @Override
    @Transactional(readOnly=true)
    public List<Example> findExampleByCompanyAndType(Company company,
            int type) {
        return sessionFactory.getCurrentSession()
                .createCriteria(Example.class)
                .add(Restrictions.eq("company.companyId",
                        company.getCompanyId()))
                .add(Restrictions.eq("type", type)).list();

    }
...
}

Answer 1

PostgreSQL doesn't implement 'dual' so you may have intended something like SELECT 1 there, for the test query. I don't know if that will fix your problems but it should help the container manage itself better.