Re: Ned to understand why all the idle connections

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(at)opensourcestrategies(dot)com>
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.