Connection leak using Hibernate SessionFactory in Spring Boot

Question

I have a Spring Boot (1.5.3) application in which I autowire the Hibernate (5.0.12) SessionFactory like this:

In application.properties:

spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate4.SpringSessionContext

In the configuration class:

@Bean
public HibernateJpaSessionFactoryBean sessionFactory() {
    return new HibernateJpaSessionFactoryBean();
}

In the @Services:

@Autowired
private SessionFactory sessionFactory;

This works well. But my problem is that after every request I get more and more idle PostgreSQL processes, and after a number of requests eventually I get this:

Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
...
[ERROR] [16.05.17 20:19:39]               DirectJDKLog.java:181  Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; 
nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
...
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
...
[ERROR] [16.05.17 20:19:39]               DirectJDKLog.java:181  Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction;
nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
...
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
...
[ WARN] [16.05.17 20:19:40]         SqlExceptionHelper.java:127  SQL Error: 0, SQLState: 53300
[ERROR] [16.05.17 20:19:40]         SqlExceptionHelper.java:129  FATAL: remaining connection slots are reserved for non-replication superuser connections
[ERROR] [16.05.17 20:19:40]               DirectJDKLog.java:181  Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; 
nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection] with root cause
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]

I use StatelessSession quite a bit for simple SQL queries, and I am 99.9% sure that I close them in the related @Service before returning the result.

I have the unconfirmed feeling that with earlier Spring Boot versions, and the pulled-in dependencies, the problem did not exist. Not sure though...

What could be the reason for these apparent leaks?

For completeness here some example uses in a @Service:

@Transactional(readOnly = true)
@Cacheable(value = "countries", key = "#root.methodName")
public List<Country> getCountries() {
    final StatelessSession session = sessionFactory.openStatelessSession();
    final Query query = session.createQuery("from Country order by id");
    final List<Country> result = query.list();
    session.close();
    return result;
}

@Transactional(readOnly = true)
public long countTimeZones() {
    final StatelessSession session = sessionFactory.openStatelessSession();
    final Long result = (Long) session.createQuery("select count(o) from TimeZone o").uniqueResult();
    session.close();
    return result;
}

@Transactional(readOnly = true)
public List<Map<String, Object>> getPhotoAlbums() {
    final StatelessSession session = sessionFactory.openStatelessSession();
    final SQLQuery query = session.createSQLQuery("select "
            + "cast(m.id as varchar), "
            + "m.name "
            // etc
            + "from media_album m "
            + "where m.account = :account "
            + "and ... "
            + "order by ...");
    query.setParameter("account", uuidOfAccount);
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    final List<Map<String, Object>> result = query.list();
    session.close();
    return result;
}

Answer 1

I could "solve" the leaks by changing the configuration like follows (Not sure all these steps are necessary though):

application.properties:

spring.jpa.properties.hibernate.current_session_context_class=
org.springframework.orm.hibernate5.SpringSessionContext

Configuration class:

add @EnableAutoConfiguration, as well as:

@Bean
public HibernateJpaSessionFactoryBean sessionFactory(EntityManagerFactory emf) {
    HibernateJpaSessionFactoryBean factory = new HibernateJpaSessionFactoryBean();
    factory.setEntityManagerFactory(emf);
    return factory;
}

This answer was helpful:

required a bean of type 'org.hibernate.SessionFactory' that could not be found

Answer 2

I believe you need to set the following property:

spring.datasource.max-active

e.g.spring.datasource.max-active=5

It looks as though the spring connection pool is opening more connections than is allowed in your postgresql.conf file