pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedbug reapper: Empty query_id in pg_stat_activity
9+ messages / 4 participants
[nested] [flat]
* bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 09:22 =?ISO-8859-1?B?ZHVvbWkucGVuZw==?= <duomi.peng@qq.com>
0 siblings, 1 reply; 9+ messages in thread
From: duomi.peng @ 2025-08-28 09:22 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
hello, everyone!
there is a report: "Empty query_id in pg_stat_activity"
https://www.postgresql.org/message-id/flat/CAJ%2B5Ff4sLuTMpg__fZH67UdaC2bWpLK1U3a8kkj_BvqO8ScYQw%40m...
Maybe it was fixed, but i reappear this bug in pg 15.14.
In my case ,just set compute_query_id = on in PostgreSQL.conf, then reproduce it:
1. in term1:
/opt/pg/15/bin/pgbench -p 1514 -i -s 10 pgbench
/opt/pg/15/bin/pgbench -p 1514 -T 1200 -M simple -d pgbench
2.in term2:
file .psqlrc \pset null 'NULL'
/opt/pg/15/bin/psql -p 1514 -t pgbench
Null display is "NULL".
psql (15.14)
Type "help" for help.
pgbench=# select query_id from pg_stat_activity where pid=7008;
3370541688642856312
pgbench=# \watch 0.2
-7505942672311739493
-7505942672311739493
NULL
-7505942672311739493
-7505942672311739493
NULL
NULL
-7810315603562552972
NULL
-7810315603562552972
NULL
4477171674326430251
-8406700390779190035
-7810315603562552972
....
NULL display the query_id
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 11:07 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: =?ISO-8859-1?B?ZHVvbWkucGVuZw==?= <duomi.peng@qq.com>
0 siblings, 1 reply; 9+ messages in thread
From: Srinath Reddy Sadipiralla @ 2025-08-28 11:07 UTC (permalink / raw)
To: duomi.peng <duomi.peng@qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Thu, Aug 28, 2025 at 3:09 PM duomi.peng <duomi.peng@qq.com> wrote:
> hello, everyone!
>
> there is a report: "Empty query_id in pg_stat_activity"
>
> https://www.postgresql.org/message-id/flat/CAJ%2B5Ff4sLuTMpg__fZH67UdaC2bWpLK1U3a8kkj_BvqO8ScYQw%40m...
>
> Maybe it was fixed, but i reappear this bug in pg 15.14.
>
> In my case ,just set compute_query_id = on in PostgreSQL.conf, then
> reproduce it:
>
> 1. in term1:
> /opt/pg/15/bin/pgbench -p 1514 -i -s 10 pgbench
> /opt/pg/15/bin/pgbench -p 1514 -T 1200 -M simple -d pgbench
>
> 2.in term2:
> file .psqlrc \pset null 'NULL'
> /opt/pg/15/bin/psql -p 1514 -t pgbench
> Null display is "NULL".
> psql (15.14)
> Type "help" for help.
>
> pgbench=# select query_id from pg_stat_activity where pid=7008;
>
> 3370541688642856312
>
> pgbench=# \watch 0.2
> -7505942672311739493
>
> -7505942672311739493
>
> NULL
>
> -7505942672311739493
>
> -7505942672311739493
>
> NULL
>
> NULL
>
> -7810315603562552972
>
> NULL
> -7810315603562552972
>
> NULL
>
> 4477171674326430251
>
> -8406700390779190035
>
> -7810315603562552972
> ....
>
>
> NULL display the query_id
>
Thanks for reporting , I was able to reproduce this locally and my
immediate observation is as we "pgstat_report_query_id(0, true);"
in exec_simple_query which resets the st_query_id to 0 and we use the same
in pg_stat_get_activity which is used to return the query_id in
the pg_stat_activity view because of this the query_id is set to NULL
in pg_stat_activity view , i might be totally wrong here ,please correct me
if i am wrong.
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 12:09 Greg Sabino Mullane <htamfids@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Greg Sabino Mullane @ 2025-08-28 12:09 UTC (permalink / raw)
To: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; +Cc: duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
If I am reading the code in utils/activity/backend_status.c correctly, I
not only see where it is happening (pgstat_report_activity), but why it is
happening as well. There is definitely a race condition as we know what the
query is before we have a chance to parse it and generate a query
identifier for it. I'm not sure of the solution - we cannot exclude null
query identifiers from the output, or callers will see the backend
disappear briefly! We cannot avoid setting it to 0, as then we could have a
query != query_id. We could store the query string somewhere and only
reveal it once we have the identifier, but that's also tricky because we
risk not setting it (and because we also set other things at the same time
as the name, which would then be further disconnected). But maybe its the
best option? I do agree this is a bug (and like Srinath, I can duplicate
easily).
Anyway, my pre-caffeine two cents.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 12:25 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Srinath Reddy Sadipiralla @ 2025-08-28 12:25 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Thu, Aug 28, 2025 at 5:40 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> If I am reading the code in utils/activity/backend_status.c correctly, I
> not only see where it is happening (pgstat_report_activity), but why it is
> happening as well.
>
sorry for half cooked analysis ,the reason is pgstat_report_query_id()
zeroes st_query_id before the new one is computed in exec_simple_query(). A
concurrent reader of pg_stat_activity during that narrow timing gap
observes 0 (displayed as NULL). The fix I tried is to retain the previous
query_id (prev_st_query_id) and return it when st_query_id is still 0 but
query_id computation is enabled, thoughts?
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
Attachments:
[application/octet-stream] v1-0001-Fix-transient-NULL-query_id-in-pg_stat_activity.patch (3.3K, ../../CAFC+b6pU_XRKZu=wF6A-9Aqp6SW1Z1vZ62Ggp3PJpuKvYM9T4A@mail.gmail.com/3-v1-0001-Fix-transient-NULL-query_id-in-pg_stat_activity.patch)
download | inline diff:
From 7cbcc4eb19958bef05d4a8ffae22221cf07813e9 Mon Sep 17 00:00:00 2001
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Thu, 28 Aug 2025 17:37:23 +0530
Subject: [PATCH 1/1] Fix transient NULL query_id in pg_stat_activity
When compute_query_id = on, pg_stat_activity.query_id could sometimes
appear as NULL even for queries that did receive a query ID. The cause
was a timing gap in exec_simple_query(): pgstat_report_query_id() resets
st_query_id to 0, and only later is the new query_id assigned. A
concurrent backend inspecting pg_stat_activity during this window would
see 0, which is displayed as NULL.
To fix, track the previous st_query_id in prev_st_query_id before the
reset. In pg_stat_get_activity(), if st_query_id is still 0 but query
ID computation is enabled, return prev_st_query_id instead of reporting
NULL.
This change avoids NULLs and makes query_id reporting in pg_stat_activity
consistent. Queries that genuinely lack a query ID
(e.g. when compute_query_id = off) continue to show NULL.
---
src/backend/utils/activity/backend_status.c | 2 ++
src/backend/utils/adt/pgstatfuncs.c | 8 +++++++-
src/include/utils/backend_status.h | 1 +
3 files changed, 10 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index a290cc4c975..f290306457f 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -320,6 +320,7 @@ pgstat_bestart_initial(void)
lbeentry.st_state = STATE_STARTING;
lbeentry.st_progress_command = PROGRESS_COMMAND_INVALID;
lbeentry.st_progress_command_target = InvalidOid;
+ lbeentry.prev_st_query_id = INT64CONST(0);
lbeentry.st_query_id = INT64CONST(0);
lbeentry.st_plan_id = INT64CONST(0);
@@ -662,6 +663,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
*/
if (state == STATE_RUNNING)
{
+ beentry->prev_st_query_id = beentry->st_query_id;
beentry->st_query_id = INT64CONST(0);
beentry->st_plan_id = INT64CONST(0);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..726321e97b5 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -31,6 +31,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/timestamp.h"
+#include "nodes/queryjumble.h"
#define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var))))
@@ -641,7 +642,12 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
* delegated */
}
if (beentry->st_query_id == INT64CONST(0))
- nulls[30] = true;
+ {
+ if(!IsQueryIdEnabled())
+ nulls[30] = true;
+ else
+ values[30] = Int64GetDatum(beentry->prev_st_query_id);
+ }
else
values[30] = Int64GetDatum(beentry->st_query_id);
}
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 3016501ac05..efa9a67cbe2 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -170,6 +170,7 @@ typedef struct PgBackendStatus
int64 st_progress_param[PGSTAT_NUM_PROGRESS_PARAM];
/* query identifier, optionally computed using post_parse_analyze_hook */
+ int64 prev_st_query_id;
int64 st_query_id;
/* plan identifier, optionally computed using planner_hook */
--
2.43.0
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 12:28 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Srinath Reddy Sadipiralla @ 2025-08-28 12:28 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Thu, Aug 28, 2025 at 5:55 PM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:
> sorry for half cooked analysis ,the reason is pgstat_report_query_id()
> zeroes st_query_id before the new one is computed in exec_simple_query(). A
> concurrent reader of pg_stat_activity during that narrow timing gap
> observes 0 (displayed as NULL). The fix I tried is to retain the previous
> query_id (prev_st_query_id) and return it when st_query_id is still 0 but
> query_id computation is enabled, thoughts?
>
I was able to reproduce this on 15.14 and master also , I think we have to
backpatch the fix.
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-28 12:42 Greg Sabino Mullane <htamfids@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Greg Sabino Mullane @ 2025-08-28 12:42 UTC (permalink / raw)
To: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; +Cc: duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Thu, Aug 28, 2025 at 8:25 AM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:
> The fix I tried is to retain the previous query_id (prev_st_query_id) and
> return it when st_query_id is still 0 but query_id computation is enabled,
> thoughts?
>
This just moves the race condition around a little - you can end up with a
query that does not agree with its query_id.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-08-31 05:02 Michael Paquier <michael@paquier.xyz>
parent: Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Paquier @ 2025-08-31 05:02 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Thu, Aug 28, 2025 at 08:42:32AM -0400, Greg Sabino Mullane wrote:
> This just moves the race condition around a little - you can end up with a
> query that does not agree with its query_id.
The query ID assignment in a backend entry has always been kind of
a pessimistic concept: the backend sets it when it knows about it.
Note that there are a few cases with the extended query protocol for
execute messages, where the query ID can be set only when we're in the
executor start path. Always reporting the previous query ID if the
current one is not set is also breaking an existing property of the
current implementation: a NULL query ID means that we are in the
process of running a query. The proposed patch would always show a
query ID, even if the backend has begun running an entire different
query, for which the query ID would be assigned later, just we don't
know about it yet.
In short, I don't think that there's anything to fix here. The
proposed patch is breaking some assumptions that the current
backend implementation relies on.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aLPXdwfnXRXONzMS@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-09-01 16:38 Greg Sabino Mullane <htamfids@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 9+ messages in thread
From: Greg Sabino Mullane @ 2025-09-01 16:38 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Sun, Aug 31, 2025 at 1:02 AM Michael Paquier <michael@paquier.xyz> wrote:
> a NULL query ID means that we are in the process of running a query.
Exactly. Maybe we document this somewhere? That would at least have given
us something we could have pointed the OP to.
In short, I don't think that there's anything to fix here.
I agree, and withdraw my "this is a bug" statement.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bug reapper: Empty query_id in pg_stat_activity
@ 2025-09-02 04:59 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Srinath Reddy Sadipiralla @ 2025-09-02 04:59 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; duomi.peng <duomi.peng@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Mon, Sep 1, 2025 at 10:09 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> On Sun, Aug 31, 2025 at 1:02 AM Michael Paquier <michael@paquier.xyz>
> wrote:
>
>> a NULL query ID means that we are in the process of running a query.
>
>
> Exactly. Maybe we document this somewhere? That would at least have given
> us something we could have pointed the OP to.
>
> In short, I don't think that there's anything to fix here.
>
>
> I agree, and withdraw my "this is a bug" statement.
>
> +1
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-09-02 04:59 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-28 09:22 bug reapper: Empty query_id in pg_stat_activity =?ISO-8859-1?B?ZHVvbWkucGVuZw==?= <duomi.peng@qq.com>
2025-08-28 11:07 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2025-08-28 12:09 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-08-28 12:25 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2025-08-28 12:28 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2025-08-28 12:42 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-08-31 05:02 ` Michael Paquier <michael@paquier.xyz>
2025-09-01 16:38 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-09-02 04:59 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
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