No per process wait stats

[jamessewell]

Hi,

I have pg_stat_statements and pg_wait_sampling loaded on PostgreSQL 11.5

postgres=# show shared_preload_libraries
;
             shared_preload_libraries
---------------------------------------------------
 timescaledb, pg_stat_statements, pg_wait_sampling
(1 row)

postgres=# \dx
                                         List of installed extensions
        Name        | Version |   Schema   |                            Description
--------------------+---------+------------+-------------------------------------------------------------------
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 pg_wait_sampling   | 1.1     | public     | sampling based statistics of wait events
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb        | 1.5.0   | public     | Enables scalable inserts and complex queries for time-series data
(4 rows)

postgres=# show pg_wait_sampling.profile_queries ;
 pg_wait_sampling.profile_queries
----------------------------------
 on
(1 row)

I am forcing a lock like this:

session 1:

begin;
truncate a;

session 2:

insert into a values (1)

This shows up as so:

postgres=# select * from pg_wait_sampling_current where event = 'relation';
 pid | event_type |  event   | queryid
-----+------------+----------+---------
 634 | Lock       | relation |       0
(1 row)

Why is queryid not populated?

[jamessewell]

Hi, It is set - I occasionally get a queryid listed. For example the SELECT from the wait sampling tables has one associated. Cheers, James Sewell

…

On Tue, 25 Feb 2020 at 12:55 am, Maksim Milyutin ***@***.***> wrote: Hi @jamessewell <https://github.com/jamessewell> ! I think the parameter pg_wait_sampling.profile_queries was not set in your case, this is default behavior. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#17?email_source=notifications&email_token=AAJJDI7NWP7APAHI3CRXNKTREPGTNA5CNFSM4K2AQHZ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEMX3TAY#issuecomment-590330243>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJJDI5PHHT25MAPF6SDIFLREPGTNANCNFSM4K2AQHZQ> .

[maksm90]

Hi @jamessewell !

It is set

Yes, I saw later so removed my last post

I occasionally get a queryid listed. For example the SELECT from the wait sampling tables has one associated

OK, I'll try to reproduce your case and get to the bottom of it .

[maksm90]

Hi @jamessewell ! Sorry for so delayed answer.

The core issue here is that the lock acquiring on relations happens on parse-analyze stage when database objects in query is transformed from test representation to internal one. But computing of queryId in pg_stat_statements extension occurs immediately after this stage (by calling post_parse_analyze_hook). And pg_wait_sampling captures queryId on further step - before planning (by calling planner_hook). For these reasons the sample collector process cannot find out the queryId of locked insert operation and eventually it detects running process as one without queryId value.

[maksm90]

In general, due to much of locks on relations and other db objects are acquired in parse-analyze routine there is no ability to assign queryId values to the waits on transactional locks, i.e., monitoring by queryId is not applicable to lock waits in most cases. And unfortunately we are not able to amend this situation because queryId is attached to Query structure that is initialized after completion of parse-anlayze stage, i.e., after primary locks acquiring on db objects in query text.

In a certain sense this issue might be solved after segregation of analyze and lock expansion stages in postgres core.

[maksm90]

In general, with current state of lock acquiring mechanics it's possible to assign queryId value to the waiting on heavyweight locks. But this assignment will be deferred. And it would require some investigation to make it correctly.