答复: empty,query_id, pg_stat_activity

No, if the query_id I provided is empty, it indicates that the statement session is in an active state, meaning the operation is ongoing. If I add "WHERE query_id IS NOT NULL" according to your method to exclude the sessions with empty queryids, there will be a problem: "Suppose there are several abnormal SQLs currently executing in the database server, causing the server's IO to be excessively high, and the corresponding query_id for these SQLs is empty. Then, I will be unable to track down that abnormal SQL. Therefore, I think this issue with the query_id might not be a bug, but it needs to be improved and fixed to assist the DBA in quickly analyzing and locating the SQL performance issues.

Session is activity When queryid is null ：

Session 1: pgbentch –I –s 10 testdb

pgbentch -T1200 testdb

session 2: psql –t<<EOF

select pid,query_id,state,query from pg_stat_activity where pid=510506;

\watch 0.2

EOF

pid | query_id | state | query

--------+----------------------+--------+-------

510506 | 3130448535078843330 | active | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (65, 6, 910745, -4761, CURRENT_TIMESTAMP);

510506 | | active | UPDATE pgbench_accounts SET abalance = abalance + -2452 WHERE aid = 516851;

510506 | | active | SELECT abalance FROM pgbench_accounts WHERE aid = 279298;

510506 | 447485537170305879 | active | UPDATE pgbench_tellers SET tbalance = tbalance + -3450 WHERE tid = 73;

发件人: Greg Sabino Mullane [mailto:htamfids(at)gmail(dot)com]
发送时间: 2025-09-02 23:11
收件人: zhouenbing <zhouenbing(at)sunwoda-evb(dot)com>
抄送: pgsql-bugs(at)lists(dot)postgresql(dot)org
主题: Re: empty,query_id, pg_stat_activity

If the query_id* is null, it is not actually running yet. This is a good thing as far as your stated tracking requirements. You can do a WHERE query_id IS NOT NULL to exclude statements where the query has appeared, but has not been parsed and executed yet. I daresay you also want to be looking at wait_event and now()-state_change as well.

Cheers,

Greg

* Actually, query_id some places, queryid in others. So annoying.

--

Crunchy Data - https://www.crunchydata.com

Enterprise Postgres Software Products & Tech Support