PostgreSQL throws "Connection has been abandoned" -> "An I/O error occurred while sending to the backend"

Question

I know that there are a lot of topics about abandoned connections , however I can't figure out what is the problem in my case, so I really hope for some help.

I have a Java application with Spring, which fetches some data from external services, and tries to save it in database.

I have the following configuration for JDBC pool

<bean id="dataSource"
          class="org.apache.tomcat.jdbc.pool.DataSource"
          p:initialSize="2"
          p:minIdle="10"
          p:maxIdle="50"
          p:maxActive="100"
          p:maxWait="15000"
          p:jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="60"
          p:logAbandoned="true"
          p:testOnBorrow="true"
          p:testOnReturn="true"
          p:testWhileIdle="true"
          p:useEquals="false"
          p:fairQueue="false"
          p:timeBetweenEvictionRunsMillis="300000"
          p:minEvictableIdleTimeMillis="300000"
          p:validationInterval="10000"
          p:validationQuery="SELECT 1">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

It's a typical Spring env, and in my service I have got a following method

@Autowired
private TransactionalWrapper wrapper;

@Override
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void reconcileAllEvents(Date from, Date to) {
   for (Event event : eventService.reconcile(from, to)) {   
            transactionalWrapper.reconcile(event);   
   }    
}

And here is what I have in TransactionalWrapper

@Service
public class TransactionalWrapperImpl implements TransactionalWrapper {

        @Autowired
        private EventRepository eventRepository;

        @Transactional(propagation = Propagation.REQUIRES_NEW)
        public void reconcile(Event event) {

            try {
                eventRepository.save(event);
            } catch (Exception e) {
                LOG.warn("Failed to handle event {}", event, e);
            }
        }
    }

There is a nightly job running and every time there are about ~10K events. I have checked that in reconcileAllEvents() there is no active transaction, and in TransactionalWrapperImpl.reconcile() new transaction is created and committed for each event.

The problem is that from time to time I am getting following exceptions

Sep 10, 2015 5:25:57 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
WARNING: Connection has been abandoned PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@3eb948ed]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1063)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:780)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:619)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)
    at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:70)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:301)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1351)
    at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:59)
    at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:67)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:110)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:420)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:257)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)

And after this one there is another one

10 Sep 2015 17:25:57,591 WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08006
10 Sep 2015 17:25:57,592 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - An I/O error occurred while sending to the backend.
javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:316)
    at com.mysema.query.jpa.impl.AbstractJPAQuery.getSingleResult(AbstractJPAQuery.java:219)
    at com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult(AbstractJPAQuery.java:295)
    at com.mysema.query.jpa.impl.AbstractJPAQuery.uniqueResult(AbstractJPAQuery.java:281)
    at com.mysema.query.support.ProjectableQuery.singleResult(ProjectableQuery.java:75)

I checked the configuration several times, and can't find anything wrong or what needs to be approved, and this keeps crashing and the events are not being saved. Note that after exception it continues to save the rest of the events in a normal way.

Any ideas what is wrong in configuration, and how it is possible to handle the issue?

Answer 1

This is usually thrown by PostgreSQL when the connection is dropped on the back-end side.

1. Try updating the driver to the latest version: 9.4-1202
2. In postgresql.conf try setting: log_statement = 'all', to see all statement log data, so you can better understand what's causing the issue.