Re: Add connection active, idle time to pg_stat_activity

Hi Sergey,

> @Aleksander Alekseev thanks for reporting the issue. I have altered
> the patch to respect the behavior of pg_stat_activity, specifically
> [1]
>
> > Another important point is that when a server process is asked to
display any of these statistics,
> > it first fetches the most recent report emitted by the collector
process and then continues to use this snapshot
> > for all statistical views and functions until the end of its current
transaction.
> > So the statistics will show static information as long as you continue
the current transaction.
>
> For the patch it means no computing of real-time values of
> total_*_time. Here is an example to illustrate the new behavior:
>
> =# begin;
>
> =*# select total_active_time, total_idle_in_transaction_time from
> pg_stat_activity where pid = pg_backend_pid();
> total_active_time | total_idle_in_transaction_time
> -------------------+--------------------------------
> 0.124 | 10505.098
>
> postgres=*# select pg_sleep(10);
>
> postgres=*# select total_active_time, total_idle_in_transaction_time
> from pg_stat_activity where pid = pg_backend_pid();
> total_active_time | total_idle_in_transaction_time
> -------------------+--------------------------------
> 0.124 | 10505.098
>
> postgres=*# commit;
>
> postgres=# select total_active_time, total_idle_in_transaction_time
> from pg_stat_activity where pid = pg_backend_pid();
> total_active_time | total_idle_in_transaction_time
> -------------------+--------------------------------
> 10015.796 | 29322.831
>
>
> [1]
https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-STATS-VIEWS

This looks reasonable.

What concerns me though is the fact that total_idle_in_transaction_time for
given session doesn't seem to updated from the perspective of another
session:

```
session1 (78376) =# BEGIN;
session1 (78376) =# select * from pg_stat_activity where pid = 78376;
...
total_active_time | 40.057
total_idle_in_transaction_time | 34322.171

session1 (78376) =# select * from pg_stat_activity where pid = 78376;
...
total_active_time | 40.057
total_idle_in_transaction_time | 34322.171

session2 (78382) =# select * from pg_stat_activity where pid = 78376;
...
total_active_time | 46.908
total_idle_in_transaction_time | 96933.518

session2 (78382) =# select * from pg_stat_activity where pid = 78376;
...
total_active_time | 46.908
total_idle_in_transaction_time | 96933.518 <--- doesn't change!

session1 (78376) =# COMMIT;
session1 (78376) =# select * from pg_stat_activity where pid = 78376;
...
total_active_time | 47.16
total_idle_in_transaction_time | 218422.143

session2 (78382) =# select * from pg_stat_activity where pid = 78376;
total_active_time | 50.631
total_idle_in_transaction_time | 218422.143
```

This is consistent with the current documentation:

> Each individual server process transmits new statistical counts to the
collector just before going idle; so a query or transaction still in
progress does not affect the displayed totals.

But it makes me wonder if there will be a lot of use of
total_idle_in_transaction_time and if the patch should actually alter this
behavior.

Thoughts?

--
Best regards,
Aleksander Alekseev