PostgreSQL idle connexions

[nemofr]

Hello.

We have setup a PostgreSQL SMP in our staging environnement on K8S/Docker. Everything works fine except that we havea slight but steady increase in the number of connections to the database. After some time, we are forced to restart the cluster of the database due to the lack of available connexions.

After analysing, we can see that "COMMIT" transactions stays in idle state as if they were not close.

We tried to setup the "jdbc.read-connections.max" configuration property to "2" but it didn't change anything.

Is there anything we can do in order to auto close connexions ?

Best Regards
Emmanuel

[phax]

Hi Emmanuel,
Honestly, I have a absolutely no clue. That's the reason why I initially prefered to have a file based backend only... :-)
The code uses by the SMP uses plain JDBC and everything is wrapped in a "commit" or "rollback".
What version of PostgreSQL are you using? I tested with v13 and v14.
hth, Philip

[nemofr]

14.0

[phax]

The idea I have atm is to ask you to enable the following logging properties in smp-server.properties:

jdbc.debug.connections=true
jdbc.debug.sql=true
jdbc.debug.transactions=true

Please try again with these debug logging enabled and look at the logfiles for anomalies.

[phax]

@ebagot did you find the reason for the problem?

[nemofr]

Hello @phax ,

We are still searching the reason of those not closed idle connexions. We don't see anything strange in the logs and we'll try an other setup of database. I'll keep you informed if we see anything phoss-smp relevant.

Regards,
Emmanuel