Re: Ned to understand why all the idle connections

Thanks for answering my questions.

Sorry I didn't mean to "top post" I thought that my other email got lost
because I had sent it to lists.postgresql.org

-----
Si Chen
Open Source Strategies, Inc.

Our Mission: https://www.youtube.com/watch?v=Uc7lmvnuJHY

On Thu, Apr 23, 2020 at 2:31 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>