PostgreSQL DB connections not being released with 42.7.5 JDBC driver

[SebastianZ]

Hi there!

We recently updated our dev environment to the latest version of Lucee. In this context, we’ve also updated the underlying JVM and Tomcat versions.

Setup now:

OS: Debian 6.1.0-22-amd64
Java Version: 21.0.7 (Eclipse Adoptium) 64bit
Tomcat Version: 9.0.105
Lucee Version: 5.4.7.3
PostgreSQL extension: 42.7.5

Previous setup:

OS: Debian 6.1.0-22-amd64
Java Version: 17.0.15+6-Debian-1deb12u1 / OpenJDK Runtime Environment Temurin-11.0.22+7 (build 11.0.22+7)
Tomcat Version: 9.0.86
Lucee Version: 5.4.5.23
PostgreSQL extension: 42.7.3

Generally, the updated versions work as expected. Though we now experience one issue: Idle connections to our PostgreSQL DB don’t seem to get released anymore, so the number of connections is steadily increasing.

For testing, we’ve reduced the connection limit from 100 down to 50 and and the connection idle timeout from 5 min to 1 min. That didn’t have any effect, though.

Any idea how to fix that issue?

Sebastian

[carehart]

That’s a lot of change at once, of course. I understand: ya gotta do what ya gotta do.

So first, have you tried simply reverting the Postgres driver back to what it was? That would seem to have the most direct impact on the db connection management, so is worth ruling out first. Then the Lucee version. (And some will suggest you try 6 or 7, but I appreciate that may seem to be “too much work” at this point.)

Second, it’s not clear: did anything about Postgres itself change? Or the server on which it runs, if it’s separate from Lucee’s?

Finally, as for the upgrades regarding Lucee, were those done in-place, or as a new Lucee install? And if the latter, is it on a different server than the first? (We can’t presume either way, since you show your OS version being unchanged.)

Sorry for only questions, but they may help narrow the path to resolution.

[psarin]

@SebastianZ

5.4.7.3

Known issue. See DB Connection idle timeout isn't honored in Lucee 6 - #2 by psarin and Jira (and the history specific to v5.x).

If you are able to upgrade to the latest RC for 6.2.2, it may solve your problem. However, it is a big upgrade (upgrade of Tomcat + Java).

[Zackster]

Does the problem still occur with the older 42.7.3 JDBC driver?

There really aren’t that many changes to 5.4 between those two LTS releases

Comparing 5.4.5.23...5.4.7.3 · lucee/Lucee

I just published the latest 42.7.7 postgres driver (it’s a minor CVE update, the 42.7.6 release had a lot of fixes)

https://luceeserver.atlassian.net/browse/LDEV-5661

Another user had also reported problems with the 42.7.5 driver

PostgreSQL queries are getting stuck on ClientRead

Hello all, I am using Lucee 5.4.7.3 with PostgreSQL Extension 42.7.5. Our Postgres database is on version 17.5. We’ve recently started having issues with UPDATE statements (and sometimes INSERT statements) getting stuck in the database with a “ClientRead” wait event (“Waiting to read data from the client”). This usually indicates that it’s waiting on the client to send more data to complete the query. [image] These queries have been in use and unchanged in my application for years, and the…

[SebastianZ]

Thank you for the quick replies! And excuse my late reply!

@carehart

So first, have you tried simply reverting the Postgres driver back to what it was?

That’s what delayed my reponse. We were checking the different PostgreSQL driver versions when I posted my question. And that took several days to get proper results.

We can now confirm that 42.7.5 is the culprit. 42.7.3 was working fine. We’ll try out 42.7.7 once available, though it doesn’t seem to be offered yet in the Lucee admin.

To answer the other questions and for reference if anybody else runs into problems when upgrading:

@carehart

Then the Lucee version. (And some will suggest you try 6 or 7, but I appreciate that may seem to be “too much work” at this point.)

@psarin

If you are able to upgrade to the latest RC for 6.2.2, it may solve your problem. However, it is a big upgrade (upgrade of Tomcat + Java).

We also just started to (finally) evaluate the upgrade to 6.2, though wanted to upgrade to the latest bugfix release of 5.4, first.

@carehart

Second, it’s not clear: did anything about Postgres itself change? Or the server on which it runs, if it’s separate from Lucee’s?

No, the PostgreSQL version did not change, nor the (separate) servers on which Lucee and the DB run.

@carehart

Finally, as for the upgrades regarding Lucee, were those done in-place, or as a new Lucee install? And if the latter, is it on a different server than the first? (We can’t presume either way, since you show your OS version being unchanged.)

The upgrade was done in-place. The upgrade to 6.2 will be a new install.

Thank you all for your help!

Sebastian

[Zackster]

there was a caching issue on the extension update provider, just deployed an update to address that.

42.7.7 should show up now in the admin?

[SebastianZ]

Yep, now it’s available. Thanks!

Installed it. I’ll provide feedback once we know whether this version fixed the issue or not.

Sebastian

[moonpo]

with 6.2.2.54-RC and 42.7.7 the issue seems to be resolve for us.

[carehart]

@SebastianZ, great to hear. Hope it works out well.

And @moonpo, do you work with him, or are you just saying you had the same problem?

[moonpo]

@carehart we were facing the issue somehow I see that my previous message didn’t come up into the thread properly.

Our previous stack was like that
Lucee 6.2.1.122
Java 21.0.7 (Eclipse Adoptium) 64bit
PostgreSQLextension 42.7.5
Apache Tomcat/11.0.6
OS Linux (6.8.0-1029-aws) 64bit

And now upgrading to the new RC version and the new postgresql driver everything is running smooth.

[carehart]

Ah, thanks for the clarifications.