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.g.johns...@gmail.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 <sic...@opensourcestrategies.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. > >
