PostgreSQL connection limit exceeded for non-superusers

Question

I am using spring application and I get the following exception:

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

When I manually try to connect to the database using DBVisualizer, I get the 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 get this error because I did not add the following line in spring -context.xml

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

Will this line be added to solve my problem. I am afraid that my application creates a connection but does not release the database connection because I did not add the above line to spring -context.xml .. Note. I do not use HibernateTemplate. I use sessionFactory.getCurrentSession().createQuery("").list() to run my queries My information in Context.xml

 <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.

Answer 1

The problem is data source configuration

The default value for the maximum number of connections is greater than the maximum number set in postgres, and when hibernate requests another connection, the data source tries to create one.

Can you show the configuration of your data source?

Answer 2

(recording steps I took with the same problem)

• Check your data source - find the maxActive number.
• Check your Postgresql settings: SELECT * FROM pg_settings - find the line with max_connections .

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

In addition, do not forget to save some backup connections for other servers accessing the same database, as well as some database management tools :)