relationship of backend_start, query_start, state_change

If you use a connection pooler, this would likely be expected behavior
since the connection is getting reused many times. Else, some app is
connected and not closing their connection between queries. At least they
aren't idle in transaction though.

On Thu, Apr 23, 2020 at 6:37 PM Si Chen <sichen@opensourcestrategies.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/

On Thu, Apr 23, 2020 at 9:37 AM Si Chen <sichen@opensourcestrategies.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

David J.

Hi David,

On Thu, Apr 23, 2020 at 6:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Apr 23, 2020 at 9:37 AM Si Chen <sichen@opensourcestrategies.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

David J.

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

--
Olivier Gautherot

On Thu, Apr 23, 2020 at 9:55 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Apr 23, 2020 at 9:37 AM Si Chen <sichen@opensourcestrategies.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

Specifically, you are including multiple states but then don't look at
which one is actually assigned. You should be able to reason about a
connection mainly from its state and not consider the query at all - it is
a debugging aid only.

David J.

On Thu, Apr 23, 2020 at 9:58 AM Olivier Gautherot <ogautherot@gautherot.net>
wrote:

Hi David,

On Thu, Apr 23, 2020 at 6:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Apr 23, 2020 at 9:37 AM Si Chen <sichen@opensourcestrategies.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.