PostgreSQL connection limit exceeded for non-superusers

Question

I am using a spring application and I am getting following exception as:

Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection.

When I am manually trying to connect to the database using DBVisualizer I am getting following error

An error occurred while establishing the connection:

Long Message:
FATAL: connection limit exceeded for non-superusers

Details:
   Type: org.postgresql.util.PSQLException
   Error Code: 0
   SQL State: 53300

Here is my spring-context.xml file

<jee:jndi-lookup id="dataSource1" jndi-name="jdbc/PmdDS"/>


    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource1" />
        <property name="configLocation">
            <value>classpath:hibernate.cfg.xml</value>
        </property>
        <property name="configurationClass">
            <value>org.hibernate.cfg.AnnotationConfiguration</value>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="current_session_context_class">thread</prop>
                <prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>

            </props>
        </property>
    </bean>

My question is I am getting this error because that I have not added following line in spring-context.xml

 <prop key="hibernate.connection.release_mode">auto</prop>

Will adding this line will solve my problem. I am fearing that my application is creating connection but not releasing the database connection because I have not added the above line in spring-context.xml.. Note I am not using HibernateTemplate . I am using sessionFactory.getCurrentSession().createQuery("").list() to fire my queries My Context.xml details

<Context>
     Specify a JDBC datasource 
    <Resource name="jdbc/PmdDS" 
              auth="Container"
              type="javax.sql.DataSource" 
              username="sdfsfsf" 
              password="sfsdfsdf" maxActive="-1"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://111.11.11.11:5432/test"/>


</Context>

Please suggest any solution

Solution 1

The problem is in the configuration of the datasource

The default value for max number of connections is higher then max number set in postgres and when hibernate asks for another connection the datasource tries to create one.

Can you show the configuration of your datasource?

Solution 2

(recording the steps I took with the same problem)

1. Check your datasource - look for maxActive number.
2. Check your Postgresql settings: SELECT * FROM pg_settings - look for the line with max_connections.

If the first number is bigger than the second, you have a problem. Either lower the first or increase the second (in postgresql.conf of your instance).

Also, do not forget to keep some reserve connections for other servers accessing the same DB, as well as some DB management tools:)

Comments

[František Hartman]

Try setting maxActive="-1" to value less than 100 (postgres default). Usually 10 to 20 is sufficient.