queryid equals 0 for some wait events

[DmitryNFomin]

Good evening
I've noticed that for real client backend pids (not PG server processes) some wait events always have 0 in queryid column in all pg_wait_sampling views. So far I found that all IPC type events, SyncRep and WALWrite events have queryid = 0, for example

select event_type, event, queryid, count(*) from pg_wait_sampling_profile where event_type = 'IPC' group by event_type, event, queryid;
 event_type |          event           | queryid | count
------------+--------------------------+---------+-------
 IPC        | CheckpointDone           |       0 |    14
 IPC        | CheckpointStart          |       0 |     6
 IPC        | MessageQueueInternal     |       0 |     1
 IPC        | MessageQueueReceive      |       0 |     1
 IPC        | ProcArrayGroupUpdate     |       0 | 13750
 IPC        | RecoveryConflictSnapshot |       0 |     1
 IPC        | SafeSnapshot             |       0 |     1
 IPC        | SyncRep                  |       0 | 21348
 IPC        | XactGroupUpdate          |       0 |  1148

select event_type, event, queryid, count(*) from pg_wait_sampling_profile where event in ('WALWrite', 'SyncRep') group by event_type, event, queryid;
 event_type |  event   | queryid | count
------------+----------+---------+-------
 LWLock     | SyncRep  |       0 |  8886
 IPC        | SyncRep  |       0 | 21359
 IO         | WALWrite |       0 |  7337
 LWLock     | WALWrite |       0 | 20102

show pg_wait_sampling.profile_queries;
 pg_wait_sampling.profile_queries
----------------------------------
 all

PG version is 13.18

as far as I understood from source code 0 is set for queryid if nesting_level == 0 but I can't understand why it happens for that wait events, could you please help me with it

[DmitryNFomin]

if I select pg_stat_activity backends with SyncRep wait event it's always have smth in query column (commit or real query)

postgres=# select pid, backend_type, substring(trim(query, E' \t\n'),0,47), wait_event, wait_event_type from pg_stat_activity where wait_event='SyncRep';
   pid   |  backend_type  |                   substring                    | wait_event | wait_event_type
---------+----------------+------------------------------------------------+------------+-----------------
 2334977 | client backend | COMMIT                                         | SyncRep    | IPC
 2319779 | client backend | COMMIT                                         | SyncRep    | IPC
 2336191 | client backend | -- queryname.xml - | SyncRep    | IPC
 2339251 | client backend | COMMIT                                         | SyncRep    | IPC
 2336036 | client backend | -- queryname.xml - Tx.insertAcqui | SyncRep    | IPC
 ...

or

SELECT w.pid, w.event_type, w.event, w.queryid, a.state, substring(trim(a.query, E' \t\n'),0,47)
FROM pg_wait_sampling_current w
JOIN pg_stat_activity a ON w.pid = a.pid
WHERE w.event = 'SyncRep';
   pid   | event_type |  event  | queryid |        state        |                   substring
---------+------------+---------+---------+---------------------+------------------------------------------------
 2309673 | IPC        | SyncRep |       0 | active              | -- queryid.xml - Tx.insert
 2335584 | IPC        | SyncRep |       0 | active              | insert into table1        
 2339305 | IPC        | SyncRep |       0 | active              | insert into table1        
 2333075 | IPC        | SyncRep |       0 | active              | -- mybatis/transaction/queryid.xml -
 2319112 | IPC        | SyncRep |       0 | active              | -- mybatis/transaction/queryid.xml -
 2336591 | IPC        | SyncRep |       0 | active              | insert into table1        
 2335667 | IPC        | SyncRep |       0 | active              | insert into table1        
 2337833 | IPC        | SyncRep |       0 | active              | insert into table1        
 2334820 | IPC        | SyncRep |       0 | active              | insert into table1        
 1139511 | IPC        | SyncRep |       0 | active              | select "config".forwardConfirm(116, 105,
 2322021 | IPC        | SyncRep |       0 | active              | insert into table2                         
 2316189 | IPC        | SyncRep |       0 | active              | COMMIT
 2319747 | IPC        | SyncRep |       0 | active              | COMMIT
 2316154 | IPC        | SyncRep |       0 | active              | COMMIT
 2317074 | IPC        | SyncRep |       0 | active              | COMMIT
 ...
(79 rows)

[Medvecrab]

Greetings

In general case, we use existing PostgreSQL hooks to track query execution. To be exact we use various executor hooks. All activities you have described (IPC, SyncRep and WALWrite) do not go through the executor and thus query_id is never set for them

We set query_id at the start of query execution and zero it when query finishes execution. nesting level is needed to track queries with subqueries. We also track query_id the same way during planning.

For IPC and WALWrite events we don't have any available hooks to log those as we do with regular query execution

[DmitryNFomin]

many thanks for clarification.
Do you think it could be good idea to track last executed query id for pid (like in pg_stat_activity), to give better visibility?
On very active systems (with hundreds of active backends and sub ms queries) _history view can not be used for purpose from my point of view

[Medvecrab]

Do you think it could be good idea to track last executed query id for pid (like in pg_stat_activity), to give better visibility?

If we would save query_id of last executed query until we got a new one we would run into a problem - we would get a lot of ClientRead wait events with last saved query_id; but those waits have nothing to do with last executed query, we just wait for user to input next query

Also we use the same way of tracking query_id as pg_stat_statements so we could join both views. With "lingering" query_ids those joins could be inaccurate

[Medvecrab]

If you have further questions - feel free to ask here or in a new issue, but for now I'll close it