B4J Question Does ConnectionPool work the same with postgres as mysql

[j_o_h_n]

I am getting the error below every now and then from my server program. It is running on a windows server vps and the database I am using is postgres rather than mysql.

com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@4d154ccd -- timeout at awaitAvailable()

When I get this error I have to restart the server program to get it working again.
This is happening during my testing when there is a very light load on the server, a remote app (ie on my computer) makes requests every 30 seconds while I am making the odd request as well from a browser.
The vps is entry level and has only 2GB of RAM and one virtual processor so maybe that might be an issue.

I don't think I am leaving any connections open, For all subs doing database access the pattern i use is

Private Sub etc
     Dim sql1 As SQL = Main.pool.GetConnection
     Dim rs as resultset
     Try
      'do stuff
    Catch
        Log(LastException.message) 
    End Try
    rs.Close
    sql1.Close
End Sub

So I was wondering if there could be an issue with connection pooling not working with postgres so well, the version of postgres I am using is 9.4

 

[Erel]

ConnectionPool isn't tied to any specific database.

Add this to test whether you have a connection leak:

Dim jo As JavaObject = pool
jo.RunMethod("setDebugUnreturnedConnectionStackTraces", Array(True))
jo.RunMethod("setUnreturnedConnectionTimeout", Array(5)) '5 seconds

Make sure to remove when done.

c3p0-v0.10.1 - JDBC3 Connection and Statement Pooling - Documentation

 

[j_o_h_n]

ConnectionPool isn't tied to any specific database.

Add this to test whether you have a connection leak:

Dim jo As JavaObject = pool
jo.RunMethod("setDebugUnreturnedConnectionStackTraces", Array(True))
jo.RunMethod("setUnreturnedConnectionTimeout", Array(5)) '5 seconds

Make sure to remove when done.

c3p0-v0.10.1 - JDBC3 Connection and Statement Pooling - Documentation

Hi Erel
Thank you.
Just so I understand correctly.

I put this code in 'Sub AppStart', I think?
It will return connections to the pool that were still open after 5 seconds.
Then if my problem does not return this will tell me that I have a problem with my code (not closing connections) that I should fix and then remove this code.

 

[Erel]

I put this code in 'Sub AppStart', I think?

Yes.

 

[j_o_h_n]

Just for completeness, I did have a memory leak and it was caused by a return statement. Really basic error, sorry.

Private Sub etc
     Dim sql1 As SQL = Main.pool.GetConnection
     Dim rs as resultset
     Try
      'do stuff
      Return
    Catch
        Log(LastException.message)
    End Try
    rs.Close
    sql1.Close
End Sub

 

[Erel]

Did the settings in post #2 help you find it?

 

[j_o_h_n]

Did the settings in post #2 help you find it?

Hi Erel actually I was going to run a new version with that in but before i did that, I looked in the statistics tab for the postgres server using the pgadmin3 admin program and
there were 15 connections listed for my database, 14 of which had the same 'current query' listed so I guessed that was the cause and sure enough just below that was a return statement.
But thank you very much I learned quite a bit from your post and reading up about setDebugUnreturnedConnectionStackTraces, etc as well as it not being a postgres issue