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
