Re: Recommendations for PGBouncer interacting with HikariCP - Mailing list pgsql-jdbc

> On Aug 23, 2019, at 1:39 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>
> On Wed, 21 Aug 2019 at 21:03, Joseph Hammerman <jhammerman@squarespace.com> wrote:
> Hi pgsql-jdbc email distribution list,
>
> We have recently added PGBouncer into our stack. We have done this in part, to protect the database. Our Java
userbaseis accustomed to using HikariCP / Hibernate for connection pooling. They would prefer to continue to use the
connectionpooler, in order to not have GC churn on connection spin up and tear down (in addition to additional latency
introducedby having to perform the three-way handshake more often). 
>
>> Is there any published material / does anyone have any knowledge of how these tools should interact or not interact?
>
> JDBC should work fine with pgbouncer.

Most things do, although there are some caveats.  For example if you have anything attached to your server session
*outside*of a txn 
(say, server-side prepared statements or session vars)
and you configure your pgbouncer for transaction pooling, you can end up creating some extremely difficult to
understandbehaviors. 

https://pgbouncer.github.io/faq.html#how-to-use-prepared-statements-with-transaction-pooling

So you do have to be a little bit careful.

>
>> HikariCP wants to keep many long running idle_in_transaction connections open, which saturates the backend
connectionpool. 
>
> That's strange why is it keeping them in transaction ? One would think once you closed (returned the connection) it
shouldcommit it. 

My personal experience working with JDBC and HikariCP is that 99.9% of the time, "idle in transaction" means that some
programmersomewhere 
actually did not close their resource.  I did end up finding numerous bugs, both in my $WORK's code as well as in Java
librarieswe use, although to the 
best of my memory the roots of problems we found were never in either PgJDBC nor HikariCP.

To diagnose these nasty issues, we took a pretty extreme method: on every checkout from the HikariCP pool, we generate
aUUID and 
print a tagged stack trace to a file.  We then set `application_name` to "app_name-$UUID".

When our DBAs found an "idle in txn" connection that we did not know where it came from, we would immediately have the
appand could 
then look up the stack trace of the last allocation site using the application_name in pg_stat_activity.

From there it was usually straightforward, if painful, to diagnose the problem:  "Oh I opened a Hibernate session and
handedit off to a Reactive 
actor that threw an exception but it never got handled and so the Connection was never closed and the whole thing got
leaked. Whoops.  But look at how *fast* Reactive is!!!" 

Hope that gives you some ideas.