Re: Ned to understand why all the idle connections

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

Please don't top-post; and this is a fairly rude hijack posting given that you already have a thread going, from today no less, where you've basically asked this very same question.

On Thu, Apr 23, 2020 at 2:18 PM Si Chen <sichen@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello David & David,

I have a similar problem -- a lot of idle transactions.  I'm using the PostgreSQL JDBC driver.  The connections look like this:

 pid  | wait_event |         state_change          |         backend_start         | xact_start |          query_start          |    ?column?     | query  

-------+------------+-------------------------------+-------------------------------+------------+-------------------------------+-----------------+--------

 32506 | ClientRead | 2020-04-23 09:29:05.6793-07   | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:29:05.679275-07 | 00:00:00.000025 | COMMIT

 32506 | ClientRead | 2020-04-23 09:30:33.247119-07 | 2020-04-23 01:00:19.612478-07 |                               | 2020-04-23 09:30:33.247109-07 | 00:00:00.00001  | COMMIT

 32506 | ClientRead | 2020-04-23 09:31:31.506914-07 | 2020-04-23 01:00:19.612478-07 |                               | 2020-04-23 09:31:31.506905-07 | 00:00:00.000009 | COMMIT

 32506 | ClientRead | 2020-04-23 09:32:32.06656-07  | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:32:32.066552-07 | 00:00:00.000008 | COMMIT

 32506 | ClientRead | 2020-04-23 09:36:51.579939-07 | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:36:51.579931-07 | 00:00:00.000008 | COMMIT

It seems like they haven't been doing anything for a long time, but the state_change keeps getting updated.

If the state_change timestamp keeps changing then by definition they are doing something......

 

  Is it possible that state_change is being updated, maybe by the JDBC driver?

Directly, no, that particular field is read-only by the user and so nothing is going to directly update it.  However, as soon as the session changes state it will change as well.

The most likely answer is that your setup for JDBC includes a connection pool that is periodically checking to see if its session is still active.  You should work on trying to prove or disprove that assumption.

Do you recommend using PgBouncer with JDBC?

I try to avoid making recommendations without knowing the situation in which something is operating.  Given the level of expertise demonstrated here I would, however, advise against adding another architectural component to your setup until your understand completely what you are already working with.  If at that point you can define a problem that you want to solve, and pgBouncer would constitute a solution, then you could consider adding it.

David J.