Auto-reconnect in postgres with tomcat pool and Spring

Question

As far as I understand, the "testOnBorrow" and "validationQuery" parameters are right up my alley but they do not seem to be working as expected.

I start-up the application, run some queries and everything goes all right. Then I restart the postgres server - without restarting tomcat - to test the DataSource can handle the re-connection and all I get is this:

    This connection has been closed.; nested exception is org.postgresql.util.PSQLException: This connection has been closed.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:674)
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:729)

...

Caused by: org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:273)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:301)
    at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:99)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:67)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:99)
    at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:153)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:99)
    at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:41)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:99)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:63)
    at $Proxy35.prepareStatement(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1436)

I am using:

• Spring 3.1
• PostgreSQL 9.2.1
• Pool of connections: org.apache.tomcat.jdbc.pool 7.0.25

My Spring bean configuration is as follows:

public DataSource dataSource() {
    org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
    // from properties file
    dataSource.setDriverClassName(environment
            .getProperty("datasource.driver"));
    dataSource.setUrl(environment.getProperty("datasource.url"));
    dataSource.setUsername(environment.getProperty("datasource.username"));
    dataSource.setPassword(environment.getProperty("datasource.password"));
    // other configurations
    dataSource.setInitialSize(10);
    dataSource.setMinIdle(10);
    dataSource.setMaxIdle(100);
    dataSource.setMaxActive(100);
    dataSource.setDefaultAutoCommit(true);
    dataSource.setMaxWait(6000);
    dataSource.setJmxEnabled(true);
    dataSource
            .setJdbcInterceptors("....ConnectionState;.....StatementFinalizer");
    dataSource.setRemoveAbandoned(true);
    dataSource.setRemoveAbandonedTimeout(10);
    dataSource.setLogAbandoned(true);
    dataSource.setTestOnBorrow(true);
    dataSource.setTestOnReturn(false);
    dataSource.setTestWhileIdle(false);
    dataSource.setUseEquals(false);
    dataSource.setFairQueue(false);
    dataSource.setTimeBetweenEvictionRunsMillis(30000);
    dataSource.setMinEvictableIdleTimeMillis(30000);
    dataSource.setValidationInterval(1800000);
    dataSource.setValidationQuery("SELECT 1");

    return dataSource;
}

Any idea?

Thanks

Answer 1

Validation of the connection is only done when it is initially borrowed from the pool. Even if it was checked before every query, there would still be a period of time between the check and the query where the connection could be lost. All queries should have some form of exception handling to deal with query failure--in general, disposing of the bad connection handle, getting a new one, and retrying the query (if possible).

In the JDBC standard there's a callback method of "connectionErrorOccurred" that is supposed to get called whenever something like this occurs, but I'm not familiar enough with how JDBC and Java works to know how to use it (or even if it covers this case).

In any case, the ONLY time you can know for use if a connection is good or bad is when you try to use it and no connection pool can automatically overcome that issue without using custom calls to execute-and-retry queries, which would break the JDBC "contract" to a standard interface.