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

František Hartman · Accepted Answer · 2012-02-29 11:20:10Z

2

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?

answered Feb 29, 2012 at 11:20

František Hartman

15.1k3 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

František Hartman Over a year ago

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

vektor · Accepted Answer · 2012-07-28 09:48:39Z

2

(recording the steps I took with the same problem)

Check your datasource - look for maxActive number.
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:)

answered Jul 28, 2012 at 9:48

vektor

2,9648 gold badges46 silver badges74 bronze badges

Collectives™ on Stack Overflow

PostgreSQL connection limit exceeded for non-superusers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related