vikas malik vikas malik - 3 years ago 296
Java Question

Arval SQLException: FATAL: sorry, too many clients already in postgres

I am using a database in PostgreSQL 9.1,in which entry are coming continuously from another program . I am sending request from Ajax after 6 sec to fetch the latest entry.tomcat output window shows exception---

Arval SQLException: FATAL: sorry, too many clients already


and program is working correctly also after this.
When i check my postgres with query---

select count(*) from pg_stat_activity;


it shows that connection are increasing continuously but I close the connection after each request.I am using netbeans and struts 1.3.

long previousSNO = Long.parseLong(request.getParameter("previousSNO"));
if(previousSNO == 0)
{
sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp ORDER BY search_date DESC LIMIT 20");
prest = cb.executeSQLQuery(sb.toString());
rs = prest.executeQuery();
}
else
{
sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp WHERE sno > ? ORDER BY search_date DESC");
prest = cb.executeSQLQuery(sb.toString());
prest.setLong(1, previousSNO);
rs = prest.executeQuery();
}
rs.last();
int c = rs.getRow();
rs.beforeFirst();

if(rs!=null && c>0)
{
//code for making json resultsb from resultset here
rs.close();
}
cb.closeConnection();
response.setContentType("text/plain");
response.getWriter().print(resultsb.toString());


//and close method in connection bean is

public void closeConnection() {
try {
// st.close();
conn.close();
System.out.println("con is closed");
conn = null;

} catch (SQLException e) {
e.getMessage();
System.out.println(e.getMessage());
System.out.println("con is not closed");
}
}


Every time its print on console " con is closed";

Answer Source

You can increase the max_connections in postgres, that is not the solution though. You have resource leaks. It could be any - connection not closed, result set not closed. Please go back and check the code.

Consider using a connection pooling library like c3p0/BoneCp

A general discussion on connection pooling is here (Thanks to @sinisa229 mihajlovski)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download