has anyone come across issues when using a postgre...
# lucee
r
has anyone come across issues when using a postgres database that has connection pooling on the database side? ever since we made that change to our database to use connection pooling, our lucee application sometimes gets stuck performing INSERT or UPDATE statements. we've been able to trace this to the application not finalizing the transaction and the database is waiting for it. we're using lucee 5.4.7.3 with postgreSQL extension 42.7.5
z
with 6 we switched to apache commons pool2, 5.4 and earlier uses a custom pool which predates the apache one, are you able to try your application out with 6.2 just to see it it's resolved?
r
it's not a very reproducible issue. the issue only happens a couple times per week in production to a single insert/update statement. 1000's of queries run in a given day. also, if it makes a difference, i haven't configured any type of db connection pooling in lucee (unless it's on by default). the connection pooling is happening with PGBouncer.
z
Connection pooling is indeed enabled by default, otherwise, queries would be slow! Would you mind posting to the dev forum, you might get some useful feedback/advice from other users?
r
Sure thing. Thanks, Zac!
z
i did a bit of reading up on this, as Lucee already manages it's connections, PGbouncer and Lucee's own connection pooling may be somewhat in competition? Did you switch to PGBouncer to solve a specific problem with Lucee connecting to your database, or because it's considered good practice?
m
I'm interested in this now - it's my understanding that using PGBouncer is generally considered a good practice - if there are potential issues using it with Lucee's new connection pooling, I'd be interested in learning the tradeoffs - fwiw we're not seeing any issues right now
z
I'm just speculating here, like if your max connections in lucee are 100 and PGbouncer only allows 50? Some thing to play around in a test environment, but if it's all working then I wouldn't worry
r
We use
-1
(infinite) for connectionLimit in lucee. PGBouncer was something our DBAs applied to all databases across the organization, not just my app. I'm running a production test right now that connects to the database on a different port to circumvent PGBouncer. If after a couple of days I no longer see these errors, I can probably conclude that PGBouncer is conflicting somehow.
z
I'd never use -1 except if I was trying to break things!!
Tho as a DBA with a client app configured like that, I'd either install PGbouncer or pick up the phone 😀
r
haha. it's a remnant of a bad dataloader integration that was making way more DB calls than needed. the bandaid was to remove the connection limit. we've since fixed dataloader, but i never changed the limit back. i honestly haven't noticed any problems (until recently) and it's been like that for years. setting a sane limit will be my next thing to test.
z
(I do like breaking things under load, as it often reveals shortcomings in stuff which can be optimized)