Re: relationship of backend_start, query_start, state_change

On Thu, Apr 23, 2020 at 6:37 PM Si Chen <sichen(at)opensourcestrategies(dot)com>
wrote:

> Hello,
>
> I'm looking at my pg_stat_activity and trying to figure out what is
> causing some of these processes. I'm using this query:
>
> SELECT pid, wait_event, state_change, backend_start, xact_start,
> query_start, state_change - query_start, query from pg_stat_activity where
> datname= 'my_database' and state in ('idle', 'idle in transaction', 'idle
> in transaction (aborted)', 'disabled');
>
> and what I see is the same PID which have been around for a while in the
> same query (COMMIT), but the query_start and state_change are updated and
> close to each other:
>
> 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
>
> So can we close this thread because it's been around since 1 AM and the
> last query is always "COMMIT"? Or should we keep it open because the
> query_start keeps updating, and the state_change is basically keeping up
> with query_start?
>
> -----
> Si Chen
> Open Source Strategies, Inc.
>
> Our Mission: https://www.youtube.com/watch?v=Uc7lmvnuJHY
>
>
There is no significant harm in having this thread. Check why the client is
not disconnecting - it may have a good reason.

--
Olivier Gautherot
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/