Subject: Empty query_id in pg_stat_activity

From: Costa Alexoglou <[email-masked]>
Date: Fri, 6 Dec 2024 15:32:18 +0100

Hey folks, I am running Benchbase and pgbench at the same time just for
debugging purposes, and I notice that sometimes query_id is missing
from pg_stat_activity. Any clue why this is happening?

```
benchbase=# SELECT query_id, now() - query_start as duration, query FROM
pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend'
ORDER BY duration DESC LIMIT 5; query_id | duration | query
---------------------+------------------+------------------------------------------------------------------------
| 00:00:00.223544 | SELECT + | | s_suppkey, + | | s_name, + | | s_address,
+ | | s_phone, + | | total_revenue + | | FROM + | | supplier, + | |
revenue0 + | | WHERE + | | s_suppkey = supplier_no + | | AND total_revenue
= ( + | | SELECT + | | MAX(total_revenue) + | | FROM + | | revenue0 + | | )
+ | | ORDER BY + | | s_suppkey + | | 3080582906387216276 | 00:00:00.000032
| UPDATE pgbench_branches SET bbalance = bbalance + -4897 WHERE bid = 8;
3080582906387216276 | -00:00:00.000321 | UPDATE pgbench_branches SET
bbalance = bbalance + -377 WHERE bid = 6; 2064869707185898531 |
-00:00:00.000501 | END; 2064869707185898531 | -00:00:00.000502 | END; (5
rows)
```

Cheers,
Costa

---

From: Erik Wienhold <[email-masked]>
Date: Fri, 6 Dec 2024 16:44:29 +0100

> from pg_stat_activity. Any clue why this is happening?

What's your Postgres version?

Could be that query_id is not reported because the session is using the
extended query protocol.  This has been fixed just recently in releases
14.14/15.9/16.5/17.1.  From the 14.14 release notes:

> * Report the active query ID for statistics purposes at the start of
>   processing of Bind and Execute protocol messages (Sami Imseih)
>   https://postgr.es/c/b36ee879c
>
>   This allows more of the work done in extended query protocol to be
>   attributed to the correct query.

Another possibility is that the session just disabled compute_query_id:
https://postgr.es/m/472115375.225506.1683812791906%40office.mailbox.org

-- 
Erik

---

From: Erik Wienhold <[email-masked]>
Date: Thu, 12 Dec 2024 21:37:16 +0100

On 2024-12-10 14:33 +0100, Costa Alexoglou wrote:
> On Fri, Dec 6, 2024 at 4:44 PM Erik Wienhold <[email-masked]> wrote:
>  It was `16.1`, run with `16.6` and indeed the query_id is there.

Thanks!

Please reply to list as well.  CC'ed it now.

-- 
Erik