Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uth6p-00Cmn3-QQ for pgsql-bugs@arkaria.postgresql.org; Wed, 03 Sep 2025 06:36:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uth6p-007SnP-1m for pgsql-bugs@arkaria.postgresql.org; Wed, 03 Sep 2025 06:36:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1utcpp-006Ura-8Q for pgsql-bugs@lists.postgresql.org; Wed, 03 Sep 2025 02:02:53 +0000 Received: from zg8tmja2lje4os43os4xodqa.icoremail.net ([206.189.79.184]) by magus.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1utcpg-000Gn3-0X for pgsql-bugs@lists.postgresql.org; Wed, 03 Sep 2025 02:02:52 +0000 Received: from sunwoda-evb.com (unknown [119.146.89.130]) by hzbj-forward-1.icoremail.net (Coremail) with SMTP id AQAAfwC3gmK_obdo6usEAA--.7888S2; Wed, 03 Sep 2025 10:02:39 +0800 (CST) Received: from sunwoda-evb.com (unknown [172.27.103.90]) by mtasvr (Coremail) with SMTP id _____wBH9Nkxobdoba4PAA--.24822S3; Wed, 03 Sep 2025 10:00:18 +0800 (CST) Received: from DL000951 (unknown [172.27.103.90]) by evbmailapp01 (Coremail) with SMTP id 2_EbrADXgeEtobdoyI9LAA--.56765S2; Wed, 03 Sep 2025 10:00:14 +0800 (CST) From: "zhouenbing" To: "'Greg Sabino Mullane'" Cc: References: <000a01dc1beb$8837b910$98a72b30$@sunwoda-evb.com> In-Reply-To: Subject: =?UTF-8?Q?=E7=AD=94=E5=A4=8D:_empty=2Cquery=5Fid=2C_pg=5Fstat=5Facti?= =?UTF-8?Q?vity?= Date: Wed, 3 Sep 2025 10:00:13 +0800 Message-ID: <000201dc1c76$7d36e320$77a4a960$@sunwoda-evb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0003_01DC1CB9.8B5AE670" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQFeNHu8I9CKFzmMwCXXFbhYQqKGIQEfiG5ctXOF68A= Content-Language: zh-cn X-CM-TRANSID: 2_EbrADXgeEtobdoyI9LAA--.56765S2 X-CM-SenderInfo: 52kr3vxqel0ww6vx045rgdgv1yeou0bp/ Authentication-Results: hzbj-forward-1.icoremail.net; spf=softfail smt p.mail=zhouenbing@sunwoda-evb.com; X-Coremail-Antispam: 1UD129KBjvJXoW7trWkAw1xKr4xWr1rXF1fWFg_yoW8tF48pF 4fK34xCF1DtF1UXrZ2yw18JrZagrWrKws8JF4DWryfZ3W5CF48Xr4xCw48GFWxCFWDtw1r Z3yjkrnruanrGaDanT9S1TB71UUUUUJqnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDU0xBIdaVrnRJUUUPYb4IE77IF4wAFF20E14v26r1j6r4UM7CIcVAFz4kK6r106r15 M28lY4IEw2IIxxk0rwA2F7IY1VAKz4vEj48ve4kI8wA2z4x0Y4vE2Ix0cI8IcVAFwI0_tr 0E3s1l84ACjcxK6xIIjxv20xvEc7CjxVAFwI0_Gr1j6F4UJwA2z4x0Y4vEx4A2jsIE14v2 6rxl6s0DM28EF7xvwVC2z280aVCY1x0267AKxVW0oVCq3wAaw2AFwI0_Jrv_JF1le2I262 IYc4CY6c8Ij28IcVAaY2xG8wAqx4xG67k08I80eVW5JVWrJwAqx4xG6c804VAFz4xC04v7 Mc02F40Ew4AK048IF2xKxVWUJVW8JwAqx4xG6xAIxVCFxsxG0wAv7VC0I7IYx2IY67AKxV W3AVW8Xw1lYx0Ex4A2jsIE14v26F4UJVW0owAm72CE4IkC6x0Yz7v_Jr0_Gr1lF7xvr2IY c2Ij64vIr41l7I0Y64k_MxkIecxEwVAFwVW8JwCY0x0Ix7I2Y4AK64vIr41lcIIF0xvE2I x0cI8IcVAFwI0_Jr0_JF4lcIIF0xvE2Ix0cI8IcVCY1x0267AKxVWUJVW8JwCYIxAIcVC2 z280aVAFwI0_Jr0_Gr1lcIIF0xvEx4A2jsIEc7CjxVAFwI0_Jr0_Gr1l42xK82IYc2Ij64 vIr41l4x8a6xAajcxJMxC20s026xCaFVCjc4AY6r1j6r4UMI8I3I0E5I8CrVAFwI0_JrI_ JrWlx2IqxVCjr7xvwVAFwI0_JrI_JrWlx4CE17CEb7AF67AKxVWUXVWUAwCIc40Y0x0EwI xGrwCI42IY6xAIw20EY4v20xvaj40_Zr0_Wr1UYxBIdaVFxhVjvjDU0xZFpf9x07jJVysU UUUU= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0003_01DC1CB9.8B5AE670 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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. =20 Session is activity When queryid is null =EF=BC=9A=20 Session 1: pgbentch =E2=80=93I =E2=80=93s 10 testdb pgbentch -T1200 testdb session 2: psql =E2=80=93t< =E6=8A=84=E9=80=81: pgsql-bugs@lists.postgresql.org =E4=B8=BB=E9=A2=98: Re: empty,query_id, pg_stat_activity =20 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. =20 Cheers, Greg =20 =20 * Actually, query_id some places, queryid in others. So annoying. =20 -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support =20 ------=_NextPart_000_0003_01DC1CB9.8B5AE670 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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.

<= o:p> 

S= ession is activity =C2=A0When queryid is null =EF= =BC=9A

Session 1: pgbentch =E2=80=93I = =E2=80=93s 10 testdb

      pgbentch -T1200 =  testdb

session 2: psql = =E2=80=93t<<EOF

select = pid,query_id,state,query from pg_stat_activity where = pid=3D510506;

\watch = 0.2

EOF =

pid   = |       = query_id       | state  | query =

--------+----------------------+--------+-------<= /span>

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 =3D abalance + -2452 WHERE aid =3D = 516851;

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

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

<= o:p> 

=E5=8F=91=E4=BB= =B6=E4=BA=BA: Greg Sabino = Mullane [mailto:htamfids@gmail.com]
=E5=8F=91=E9=80= =81=E6=97=B6=E9=97=B4: = 2025-09-02 23:11
=E6=94=B6=E4=BB= =B6=E4=BA=BA: zhouenbing = <zhouenbing@sunwoda-evb.com>
=E6=8A=84=E9=80= =81: = pgsql-bugs@lists.postgresql.org
=E4=B8=BB=E9=A2= =98: 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

 

------=_NextPart_000_0003_01DC1CB9.8B5AE670--