relationship of backend_start, query_start, state_change

From: Si Chen <sichen(at)opensourcestrategies(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: relationship of backend_start, query_start, state_change
Date: 2020-04-23 16:37:40
Message-ID: CAAYSSjOYNAnjMD_sEp-GOivTezWEbGZFOVeW2MG_q-E6h86q=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Preethi S 2020-04-23 16:43:26 Re: Reg: Help to understand the source code
Previous Message Rob Sargent 2020-04-23 16:30:53 Re: Reg: Help to understand the source code