I am using hibernate in a JSP application deployed in Tomcat.

I currently have the following pool configuration:

C3P0 parameters:
<property name="hibernate.c3p0.min_size">2</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>

Code between sessionFactory.openSession() and session.close():


where the flush and releaseresources methods are:



My problem is that, having a look at the postgres processes created (for example in PgAdminIII, I see that, for each call of this query, I get one to three processes with

Current Query - <IDLE>

I am confused and don't understand what's happening - isn't the connection pool supposed to reuse connections instead of creating new ones? It seems these Idle processes just accumulate forever.

Any help greatly appreciated.

On a follow up to the question, can this be related with the fact that I'm creating a session factory on each request, like so:

Yep, I'm pretty sure that's the point. I'm not 100% certain, but I think that when using c3p0 each SessionFactory will build its own connection-pool, so my guess is you should get at least 2 (your c3p0 min_size) new connections for each SessionFactory built.
It also seems you don't close() your SessionFactory when you're done with it, cause this should release the acquired resources (so the connections should disappear).

But using a new SessionFactory per request is bad practice anyway, because building SessionFactories is really expensive - in fact it's running the complete hibernate-startup including parsing of all mapping- and configuration-files etc. Also SessionFactories use a considerable amount of resources (e.g. memory), so your current application should leak those resources heavily because of unused SessionFactories.

You should try to generate only one SessionFactory and reuse it. You could register it with JNDI or keep it as a Singleton in some HibernateUtil-class or smoething like that.

Thank you for the input.
In the meantime I followed my huntch and I believe I've solved the issue.

I am using Struts 2 + Hibernate, so I created an Interceptor responsible for putting the SessionFactory in the application context map, so it is only created once for the application.