From: zhouenbing <zhouenbing@sunwoda-evb.com>
To: 'Greg Sabino Mullane' <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: 答复: empty,query_id, pg_stat_activity
Date: Wed, 3 Sep 2025 10:00:13 +0800
Message-ID: <000201dc1c76$7d36e320$77a4a960$@sunwoda-evb.com> (raw)
In-Reply-To: <CAKAnmmKn9Q0Kw6qiQu1dvW1hkq0twOCsX4AO9=Xbk56cOh1aJw@mail.gmail.com>
References: <000a01dc1beb$8837b910$98a72b30$@sunwoda-evb.com>
<CAKAnmmKn9Q0Kw6qiQu1dvW1hkq0twOCsX4AO9=Xbk56cOh1aJw@mail.gmail.com>
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@gmail.com]
发送时间: 2025-09-02 23:11
收件人: zhouenbing <zhouenbing@sunwoda-evb.com>
抄送: pgsql-bugs@lists.postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: zhouenbing@sunwoda-evb.com, htamfids@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: 答复: empty,query_id, pg_stat_activity
In-Reply-To: <000201dc1c76$7d36e320$77a4a960$@sunwoda-evb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox