Hibernate 3.2 and apache dbcp eats postgres connections

Question

I have a simple entity bean and use hibernate 3.2.5 to persist it to postgres, prod will be pg 8.4, while I currently develop on 9.1. I use apache commons dbcp 1.4 (and commons pool 1.5.4) a public class DBCPConnectionProvider implements ConnectionProvider as googled from the web http://wiki.apache.org/commons/DBCP/Hibernate

Earlier I used c3po and mysql instead of dbcp. Now I get

Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection

and

Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

Using mysql/c3po earlier, I got similar errors.

Currently persistence.xml properties looks like this:

  <property name="hibernate.connection.username" value="marin-legacy"/>
  <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
  <property name="hibernate.connection.password" value="marin-legacy"/>
  <property name="hibernate.connection.url" value="jdbc:postgresql://localhost/database"/>
  <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
  <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
  <property name="show_sql" value="true"/>

  <property name="hibernate.connection.provider_class" value="vinter2010.persistence.DBCPConnectionProvider"/>
  <property name="hibernate.dbcp.initialSize" value="1"/>
  <property name="hibernate.dbcp.maxActive" value="5"/>
  <property name="hibernate.dbcp.maxIdle" value="5"/>
  <property name="hibernate.dbcp.minIdle" value="1"/>

Using this I would expect dbcp to keep 5 connections open and reassigning those to javacode.

Currently I have a servlet that inserts records. It succeeds with a number of insert more or less equal to the number of max_connections in postgresql.conf, which is 100. After that it fails with the above errors. Currently the servlet is called by a client that runs in loop apx 350 times, posting one row to store for the servlet in each loop.

If I run without dbcp or other pool, I get the same exception, only change is that it is thrown by hibernate and not dbcp.

I would have expected dbcp to just allocate 5 connections, and hibernate to open and close just one connection at a time.

The actual code is very simple, and basically generated by the jpa-generator in netbeans 6.5.

This is the create-function that I call from my servlet (code by Netbeans):

    public void create(WebOrderWinter2 webOrderWinter2) {
    EntityManager em = null;
    try {
        em = getEntityManager();
        em.getTransaction().begin();
        em.persist(webOrderWinter2);
        em.getTransaction().commit();
    } finally {
        if (em != null) {
            em.close();
        }
    }
}

The entity bean is equally standard, basically like Netbeans generated it, and it works flawlessly, can't imagine the connection problem lies there. It seems I have some code/configuration that makes Hibernate overrule the connection pool configuration and have the pool open new connections instead of reusing old ones. Anone have any clues here?

Back to c3po I still get same problem. The method create(...) above is called numerous times in my servlet, one for each request from the calling client, and the getEntityManager() method calls the entityManagerFactory.createEntityManager() each time. Is this the problem, should I create one entityManager instance that all the request-threads could share, somehow? — Jon Martin Solaas, Aug 06 '12 at 01:09
Kind of resolved it using tomcats builtin dbcp and defining datasource in context.xml (and providing postgresql.jar in tomcat/lib). But I still wonder how to configure a working connection pool on the application level. It'd be nice when altering the tomcat configuration isn't an option. — Jon Martin Solaas, Aug 06 '12 at 10:14

Hibernate 3.2 and apache dbcp eats postgres connections

0 Answers0