Horton

Re: Add connection active, idle time to pg_stat_activity

Sergey Dudoladov <sergey.dudoladov@gmail.com>

View thread
From: Sergey Dudoladov <sergey.dudoladov@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: torikoshia <torikoshia@oss.nttdata.com>, "Drouvot, Bertrand" <bdrouvot@amazon.com>, Andres Freund <andres@anarazel.de>, Julien Rouhaud <rjuju123@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Kuntal Ghosh <kuntalghosh.2007@gmail.com>, Rafia Sabih <rafia.pghackers@gmail.com>
Date: 2022-07-21T16:22:51Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove unstable pg_amcheck tests.

Attachments

Hello,

I have addressed the reviews.

@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

Regards,
Sergey