Re: relationship of backend_start, query_start, state_change

On Thu, Apr 23, 2020 at 9:58 AM Olivier Gautherot <ogautherot(at)gautherot(dot)net>
wrote:

> Hi David,
>
> On Thu, Apr 23, 2020 at 6:55 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Thu, Apr 23, 2020 at 9:37 AM 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');
>>>
>>>
>> Including the "state" field should clear things up considerably.
>>
>>
>> https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>>
>>
>>

> The transactions are idle, they are filtered in the WHERE statement.
>

You assume that, in this case seemingly correctly, but a failure to include
and talk about the specific state that shows up suggests a failure to
understand that the three states that have the word idle in them are
different and should be reasoned about differently.

David J.