Question: how pg_wait_sampling_reset_profile() handles queries which still run at the moment of reset?

[JosefMachytkaNetApp]

Hello guys, thank you very much for your work on this extension!

I would like to ask you here because I did not find any direct answer neither in the source code nor in documentation or articles on web.

If I use pg_wait_sampling_reset_profile() function to reset memory hash table with profile, what happens to the stats of queries which will be still running at the moment of reset? I presume they will start again from 0? If so, is there some simple way to re-initialize them so stats would continue?

Thank you very much!

[Medvecrab]

Hello!

I've conducted some tests

First one is for pg_sleep:

1. Connect to the server using two clients (I've used psql)
2. Test how much wait events does one call of pg_sleep records:

postgres=# select pg_sleep(10);
 pg_sleep
----------

(1 row)

postgres=# select * from pg_wait_sampling_profile where pid = pg_backend_pid();
 pid  | event_type |   event    | queryid | count
------+------------+------------+---------+-------
 2541 | Timeout    | PgSleep    |       0 |   994
 2541 | Client     | ClientRead |       0 | 17294
(2 rows)

You get roughly 1000 wait events per 10 seconds of pg_sleep.
3. During pg_sleep call pg_wait_sampling_reset_profile() in other client and check the results:

postgres=# select pg_sleep(10);
 pg_sleep
----------

(1 row)

>--second client; reset profile roughly 3-5 seconds after pg_sleep (done by hand, not too precise)
>postgres=# select pg_wait_sampling_reset_profile();
> pg_wait_sampling_reset_profile
>--------------------------------
>
>(1 row)

postgres=# select * from pg_wait_sampling_profile where pid = pg_backend_pid();
 pid  | event_type |   event    | queryid | count
------+------------+------------+---------+-------
 2541 | Timeout    | PgSleep    |       0 |   642
 2541 | Client     | ClientRead |       0 |   169
(2 rows)

Second case for locks on one table:

postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
          10114
(1 row)
postgres=# create table test_table (a text, b int);
CREATE TABLE
postgres=# insert into test_table values ('first', 1), ('second', 2);
INSERT 0 2
postgres=# begin;
BEGIN
>--second connection
>postgres=# begin;
>BEGIN
>postgres=*# update test_table set a = 'third' where b = 1;
>UPDATE 1
postgres=*# update test_table set a = 'fourth' where b = 1;
--now we sit here waiting
>postgres=*# select * from pg_wait_sampling_profile where pid = 10114;
>  pid  | event_type |     event     | queryid | count
>-------+------------+---------------+---------+-------
> 10114 | Lock       | transactionid |       0 |  2432
> 10114 | Client     | ClientRead    |       0 | 18975
>(2 rows)
>--wait some time, reset profile and check almost immediately
>postgres=*# select pg_wait_sampling_reset_profile();
> pg_wait_sampling_reset_profile
>--------------------------------
>
>(1 row)
>postgres=*# select * from pg_wait_sampling_profile where pid = 10114;
>  pid  | event_type |     event     | queryid | count
>-------+------------+---------------+---------+-------
> 10114 | Lock       | transactionid |       0 |   120
>(1 row)

You can see that wait events start again from 0 after reset_profile for running queries.

I don't think there is an easy way (or any way) now to save stats of running statements, since on reset_profile() call collector process deletes the whole hash-table and re-creates and empty one. We'd have to change code there to save stats of running queries on reset.

You could probably do it by saving current stats by hand to some table, resetting profile, and then sum saved stats with current stats on select (using function or something else), but even using this way you can't alter statistics of the extension itself, only do some calculations on the side

[JosefMachytkaNetApp]

Thank you very much @Medvecrab for examples and ideas!
I will try your suggestion with storing old stats and summarizing them with new ones.
Thank you very much for your help!