Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 Go to next message
Timo Rohrberg is currently offline Timo RohrbergFriend
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 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
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 #1681158 is a reply to message #1679360] Tue, 17 March 2015 07:39 Go to previous messageGo to next message
Timo Rohrberg is currently offline Timo RohrbergFriend
Messages: 69
Registered: September 2011
Location: Karlsruhe
Member
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
Re: [Teneo] PSQLException: Too many clients already [message #1681205 is a reply to message #1681158] Tue, 17 March 2015 08:01 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
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 Go to previous messageGo to next message
Timo Rohrberg is currently offline Timo RohrbergFriend
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 #1681883 is a reply to message #1681853] Tue, 17 March 2015 13:59 Go to previous messageGo to next message
Timo Rohrberg is currently offline Timo RohrbergFriend
Messages: 69
Registered: September 2011
Location: Karlsruhe
Member
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
Re: [Teneo] PSQLException: Too many clients already [message #1681894 is a reply to message #1681883] Tue, 17 March 2015 14:04 Go to previous message
Martin Taal is currently offline Martin TaalFriend
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
Previous Topic:[Edapt] Release of Eclipse-Source-Bundles
Next Topic:EMFStore vs. CDO
Goto Forum:
  


Current Time: Sat Mar 28 15:14:15 GMT 2020

Powered by FUDForum. Page generated in 0.02345 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top