Ebean + pgsql: connections remain established

[Edmundo Carmona]

Hi!

I just noticed that in my application (it's a web application using Ebean) connections remain active... a lot of them... 98 of them at the moment... and they don't disconnect... then I get a "FATAL: sorry, too many clients already" exception.

How can I avoid this? Am I doing something wrong?

[edge]

There are many reasons why you may be running into this problem. Its not really an Ebean issue but a connection management issue

Are you using a connection pool? Do you have so many concurrent web sessions? Are you closing any iterators in case you are using ebean's interators.

How are you currently closing the connections? 

[Edmundo Carmona]

I really assumed a lot of it would be taken care of with garbage
collection so I must be leaving some things _untaken care of_.

Can you point me to an article where I could read the rules of thumb for this?

For instance, I'm only testing a single script in a single session by
running it over and over again. This script is using:

Ebean.find(MyEntity.class).where().eq('somefield', somevalue).findList();

I then use the iterator of this list to go through, but a generic
iterator, then I just forget about it and don't do anything else with
it. If I have to close this iterators I'd have to cast it to Ebean's
iterator in order to close it... and that would make the code a little
bit ebean dependant instead of being truly generic (so that I can make
very small changes in case I start using another ORM.... not in sight,
but....).

[Edmundo Carmona]

Oh, and in case i'm not being clear, I'm not closing the connections.
I'm relying on Ebean to handle the DB connections. All I do is set up
the connection configuration for ebean in initialization (username,
password, driver, url), I register the entities I'll be using and
start using ebean right away.

[edge]

The connection to the database is not taken care of by the garbage collector. 

The iterator you are getting there is just a plain old java.util.Iterator so there is nothing EBean specific about that. I was referring to the a different type of iterator which behaves more like an SQL cursor.

What is the maxConnections set to in your ebean.properties file? Try setting it to lower than 99

[Edmundo Carmona]

Set it to 90

$ netstat -ntp | grep 5432 | grep java | wc -l
95

And:
java.sql.SQLException: Unsuccessfully waited [1000] millis for a
connection to be returned. No connections are free. You need to
Increase the max connections of [90] or look for a connection pool
leak using datasource.xxx.capturestacktrace=true
com.avaje.ebeaninternal.server.lib.sql.PooledConnectionQueue._getPooledConnectionWaitLoop(PooledConnectionQueue.java:333)
com.avaje.ebeaninternal.server.lib.sql.PooledConnectionQueue._getPooledConnection(PooledConnectionQueue.java:307)
com.avaje.ebeaninternal.server.lib.sql.PooledConnectionQueue.getPooledConnection(PooledConnectionQueue.java:247)
com.avaje.ebeaninternal.server.lib.sql.DataSourcePool.getPooledConnection(DataSourcePool.java:653)
com.avaje.ebeaninternal.server.lib.sql.DataSourcePool.getConnection(DataSourcePool.java:641)
com.avaje.ebeaninternal.server.transaction.TransactionManager.createQueryTransaction(TransactionManager.java:349)
com.avaje.ebeaninternal.server.core.DefaultServer.createQueryTransaction(DefaultServer.java:2079)
com.avaje.ebeaninternal.server.core.OrmQueryRequest.initTransIfRequired(OrmQueryRequest.java:200)
com.avaje.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1520)
com.avaje.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:902)
la.cps.sms.entities.MessageDirection.getAllMessageDirections(MessageDirection.java:77)
la.cps.sms.web.control.MessageDirection.putCmbDireccionMensaje(MessageDirection.java:18)
org.apache.jsp.admin.paginas.vista.lstMensaje_jsp._jspService(lstMensaje_jsp.java:221)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

[Edmundo Carmona]

What is the way to get the connection from ebean by hand so that I
close it manually?

[Edmundo Carmona]

Ebean.currentTransaction().getConnection().close();

Even if I haven't started a transaction (with beginTransaction(), I mean).

[Edmundo Carmona]

Nope.... that didn't work cause I haven't started a transaction, I suppose.

java.lang.NullPointerException
la.cps.sms.entities.AbstractEntity.closeConnection(AbstractEntity.java:87)
org.apache.jsp.admin.paginas.vista.lstMensaje_jsp._jspService(lstMensaje_jsp.java:423)

[Edmundo Carmona]

And by the way, I have this when configuring Ebean:

dsConfig.setMaxInactiveTimeSecs(5);

dsConfig being an instance of DataSourceConfig. Yet connections never
disconnect.

[Edmundo Carmona]

As I read around, I think the problem is in the connection pooler.
What's the point of closing the connections, really? They should be
pooled so that they can be reused.... but then it seems like
connections are not being reused at all. What's the way to debug this?
Any articles around there illustrating pooling with ebean?

[Edmundo Carmona]

Nailed it. I was using a QueryIterator that wasn't getting closed.

Thanks for your kind help, edge. It's been very insightful.

[edge]

hmmm - I did mention that in my first reply but the code you posted wan't using a QueryIterator!