Home » Modeling » EMF "Technology" (Ecore Tools, EMFatic, etc) » [Teneo] PSQLException: Too many clients already(How to initialize, open, and close EntityManager(Factory) correctly to avoid "too many clients" exception.)
[Teneo] PSQLException: Too many clients already [message #1679178] |
Mon, 16 March 2015 14:00 |
Timo Rohrberg Messages: 69 Registered: September 2011 Location: Karlsruhe |
Member |
|
|
Hello everybody,
once again, I have a small problem with using Eclipse Teneo correctly. I do have a simple OSGi-based JSE application at hand providing database access via an OSGi service. Within the implementation of that service, I instantiate and initalize a HbEntityDataStory lazily and create the EntityManager from it:
protected EntityManager getEntityManager() {
if (dataStore == null) {
dataStore = new HbEntityDataStore();
dataStore.setName("HbEntityDataStore");
dataStore.setEPackages(/* My EPackages registered here. */);
}
return dataStore.createEntityManager();
}
In my code to access the database, I then use that method to gain access to the DB:
public EObject query(EClass eClass, String id) {
EntityManager em = getEntityManager();
try {
/* Do database query here. */
} finally {
em.close();
}
}
To me everything seems fine: I instantiate the EntityManager when needed - hoping that Teneo would recycle it - and close it after each transaction.
However, after a couple of database accesses, I get a PSQLException stating that there are "too many clients already". Note: It does not say "too many sessions opened" already, but "to many clients" which I would translate with "too many connections".
What is going wrong here? What am I missing? Should I manage, instantiate and recycle my EntityManager my own? Unfortunately, I do not have too much knowledge of JPA, but was hoping that Eclipse Teneo would manage all that for me...
Does anyone of you have any hints for me?
Thanks for any hints.
Best regards
Timo
|
|
|
Re: [Teneo] PSQLException: Too many clients already [message #1679360 is a reply to message #1679178] |
Mon, 16 March 2015 15:34 |
Martin Taal Messages: 5468 Registered: July 2009 |
Senior Member |
|
|
Hi Timo,
Yes entitymanager close should work I think also, but maybe you also create a transaction and don't finish it:
http://stackoverflow.com/questions/19107408/entitymanager-close-not-closing-connections-to-database
Some other links:
https://forum.hibernate.org/viewtopic.php?f=1&t=1009693&sid=337998ac451d8e6dad38e745192a139d
http://stackoverflow.com/questions/25017756/how-to-release-jdbc-connection-after-accessing-entities-outside-the-transaction
Do you use a connection pooling lib? What are the configuration parameters for Hibernate?
gr. Martin
On 16-03-15 15:00, Timo Rohrberg wrote:
> Hello everybody,
>
> once again, I have a small problem with using Eclipse Teneo correctly. I do have a simple OSGi-based JSE application at
> hand providing database access via an OSGi service. Within the implementation of that service, I instantiate and
> initalize a HbEntityDataStory lazily and create the EntityManager from it:
>
>
> protected EntityManager getEntityManager() {
> if (dataStore == null) {
> dataStore = new HbEntityDataStore();
> dataStore.setName("HbEntityDataStore");
> dataStore.setEPackages(/* My EPackages registered here. */);
> }
>
> return dataStore.createEntityManager();
> }
>
>
> In my code to access the database, I then use that method to gain access to the DB:
>
>
> public EObject query(EClass eClass, String id) {
> EntityManager em = getEntityManager();
> try {
> /* Do database query here. */
> } finally {
> em.close();
> }
> }
>
>
> To me everything seems fine: I instantiate the EntityManager when needed - hoping that Teneo would recycle it - and
> close it after each transaction.
>
> However, after a couple of database accesses, I get a PSQLException stating that there are "too many clients already".
> Note: It does not say "too many sessions opened" already, but "to many clients" which I would translate with "too many
> connections".
>
> What is going wrong here? What am I missing? Should I manage, instantiate and recycle my EntityManager my own?
> Unfortunately, I do not have too much knowledge of JPA, but was hoping that Eclipse Teneo would manage all that for me...
>
> Does anyone of you have any hints for me?
>
> Thanks for any hints.
>
> Best regards
> Timo
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
|
|
| |
Re: [Teneo] PSQLException: Too many clients already [message #1681205 is a reply to message #1681158] |
Tue, 17 March 2015 08:01 |
Martin Taal Messages: 5468 Registered: July 2009 |
Senior Member |
|
|
Hi Timo,
I my view it is better to explicitly create a transaction also for read only queries. And also explicitly manage the
transaction (depends if you have auto-commit on, which normally you don't want to be on/true anyway). So I would try
this first and see if it helps.
If it does not help then the next thing is to re-check your code.. My experience (with my own code :-) is that
connection leaking is almost always caused by application code not handling connections/transactions completely correct.
gr. Martin
On 17-03-15 08:39, Timo Rohrberg wrote:
> Hi Martin,
>
> thanks for your answer.
>
> I reviewed my code once again and I do close all transactions correctly. In a pure query method (only read access to
> database) I am not using a transaction at all (no beginn, no commit, no rollback). Is this a problem?
>
> All the other links did unfortunately not help me solve the problem.
>
> Furthermore, I am not using any connection pooling library - even though this is something to think about for the
> project's future. I originally assumed that teneo is doing all this stuff for me.
>
> I also don't have any special hibernate configuration parameters. I just set the following settings via JVM command line
> parameters:
>
>
> -Dhibernate.connection.driver_class=org.postgresql.Driver
> -Dhibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect
> -Dhibernate.connection.url=jdbc:postgresql://[DBHOST]:5432/[DBNAME]
> -Dhibernate.connection.username=[DBUSER]
> -Dhibernate.connection.password=[DBPASSW]
>
>
> And I initialze the hibernate entity data store as follows:
>
>
> private EntityManager getEntityManager() {
> if( dataStore == null ) {
> dataStore = new HbEntityDataStore();
> dataStore.setName( HIBERNATE_ENTITY_DS );
> dataStore.setEPackages( new EPackage[] { /* My Ecore Packages. */ } );
> }
>
> return dataStore.createEntityManager();
> }
>
>
> Do you have any further hints for me what might go wrong here? This is a very simple data access layer encapsulated
> within a simple OSGi service. I cannot imagine anymore what is wrong here.
>
> Thanks for any further hints.
>
> Cheers
> Timo
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
|
|
|
Re: [Teneo] PSQLException: Too many clients already [message #1681853 is a reply to message #1681205] |
Tue, 17 March 2015 13:42 |
Timo Rohrberg Messages: 69 Registered: September 2011 Location: Karlsruhe |
Member |
|
|
Hi Martin,
thanks again for your answer.
I tried to add an explicit transaction into my query method and rollback and commit it respectively after executing the query. Unfortunately, it did not help anything.
Attached, you find my persistence service implementation in which I removed all irrelevant source code to a) make it more clearly understandable and b) avoiding violation of intelectual property constraints of my company.
As you can see, the implementation is quite straight forward. Further analysis revealed that with pgAdmin and the following SQL statement on my database
select * from pg_stat_activity WHERE datname = 'myDB'
I can see an ever increasing numer of connections/sessions on the database while running my complete set of tests. All of the connections/sessions, but the last one are stated to be "<IDLE>", and after all tests completed (or failed), all the connections/sessions are gone again.
On my local DB server on my development machine, all tests pass. But on the remote DB server running multiple PostgreSQL databases, the final 10 tests or so fail with the PSQLException "too many clients already". The attached HTML file shows the list of open connections/sessions shortly before the last test fails.
Do you have any further ideas how to track this problem down and solve it?
Edit: For creating the attached HTML file listing the open connections/sessions, I ran my tests in debug mode and stopped it in the very last tests to make the query for open connections/sessions. When continuing the execution later, the final test succeeded! So it seems there is a connection/session timeout issue here. The connections/sessions are closed after a while when breaking the execution with the debugger. Maybe this helps to further track down the problem. I still think that I have too many open connections/sessions.
Second edit: I additionally observed now that while running my tests, I do have an ever increasing number of real TCP connections to the DB server mapping the number of open connections shown with the SQL command stated above. This does not feel right! What is going wrong here? Is this a configuration issue?
Cheers
Timo
[Updated on: Tue, 17 March 2015 13:50] Report message to a moderator
|
|
| |
Re: [Teneo] PSQLException: Too many clients already [message #1681894 is a reply to message #1681883] |
Tue, 17 March 2015 14:04 |
Martin Taal Messages: 5468 Registered: July 2009 |
Senior Member |
|
|
Hi Timo,
Good that you found it. I am more into web apps and there I like the following pattern:
- create an EntityManagerFactory when the application starts
- for each (http) request create an EntityManager on request
- commit and close the entitymanager at the end of the request (rollback instead of commit in case of an error)
Not sure if your tests also change/add to the model itself, if so you would still need a EntityManagerFactory for each
test separately. In any case it can make sense to create an EntityManager in the @Before but then you need to close it
in the @After method.
Or another approach, do the EntityManager creation on demand but in the @After always check if it was created and close it.
gr. Martin
On 17-03-15 14:59, Timo Rohrberg wrote:
> Hi Martin,
>
> seems, I found the problem while writing and editing the previous reply: For my unit tests, I am instantiating the
> HibernatePersistenceService within a @Before method. This of course instantiates the service and with it the
> HbEntityDataStore, EntityManagerFactory and EntityManager for each and every test (method). Moving the instantiation of
> the service into a @BeforeClass method already solved the problem of failing tests as there are less instances created
> then, of course.
>
> However, I still feel bad instantiating the HibernatePersistenceService within the @BeforeClass method. I will look for
> a way to only have ONE instance of the service for the complete run of tests.
>
> Thanks for your patience and your help.
>
> Cheers
> Timo
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
|
|
|
Goto Forum:
Current Time: Tue Sep 24 05:38:03 GMT 2024
Powered by FUDForum. Page generated in 0.03818 seconds
|