Re: Add connection active, idle time to pg_stat_activity - Mailing list pgsql-hackers

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.


--
Best regards,
Aleksander Alekseev