Incorrect query_id persistence when pg_hint_plan.enable_hint_table is enabled

[jjja5555]

This can be reproduce in psql

• create extension pg_hint_plan;
• set pg_hint_plan.enable_hint_table = on;

postgres=# select version();
                                                           version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.1 (Homebrew) on aarch64-apple-darwin24.6.0, compiled by Apple clang version 17.0.0 (clang-1700.4.4.1), 64-bit
(1 row)

Session 1

load 'pg_hint_plan';
create extension pg_hint_plan;
set pg_hint_plan.enable_hint_table = on;
postgres=# set pg_hint_plan.enable_hint_table = on;
SET
postgres=# select pg_sleep(10) from t2;
 pg_sleep 
----------
 
(1 row)

postgres=# select pg_sleep(10) from t3;
 pg_sleep 
----------
 
(1 row)

Session 2 monitoring process

postgres=# select query_id, query from pg_stat_activity where pid = 15282;
      query_id       |            query             
---------------------+------------------------------
 9119088274641895962 | select pg_sleep(10) from t2;
(1 row)

postgres=# select query_id, query from pg_stat_activity where pid = 15282;
      query_id       |            query             
---------------------+------------------------------
 9119088274641895962 | select pg_sleep(10) from t3;
(1 row)

The hint_plan.hints table lookup query (shown below) sets backendStatus first, preventing the actual user query from setting its own query_id since backendStatus is already non-zero:

const char *search_query =
		"SELECT hints "
		"  FROM hint_plan.hints "
		" WHERE query_id = $1 "
		"   AND ( application_name = $2 "
		"    OR application_name = '' ) "
		" ORDER BY application_name DESC";

p = SPI_prepare(search_query, 2, argtypes);
plan = SPI_saveplan(p);
SPI_execute_plan(plan, values, nulls, true, 1);

This explains why query_id 9119088274641895962 persists across different queries (pg_sleep(10) from t2 and pg_sleep(10) from t3) when pg_hint_plan.enable_hint_table is enabled.

[samimseih]

Hi,

Thanks for the report.

This is a bug as it breaks a users monitoring from pg_stat_activity (pg_stat_statements is not impacted).

Both pgstat_report_query_id and pgstat_report_plan_id [0] when called with 'force' = false, which is how it's called by the planner and executor, will not override an already set identifier. In this case, the identifier already set is the one of the query against the hint_table.

I think one possible solution is something like this, where we reset the query_id and plan_id after the hint table query is executed.

diff --git a/pg_hint_plan.c b/pg_hint_plan.c
index 3e7eaf6..6d2f8c0 100644
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -40,6 +40,7 @@
 #include "parser/scansup.h"
 #include "partitioning/partbounds.h"
 #include "tcop/utility.h"
+#include "utils/backend_status.h"
 #include "utils/builtins.h"
 #include "utils/float.h"
 #include "utils/lsyscache.h"
@@ -1992,6 +1993,9 @@ get_hints_from_table(uint64 queryId, const char *client_application)
 
                SPI_execute_plan(plan, values, nulls, true, 1);
 
+               pgstat_report_query_id(0, true);
+               pgstat_report_plan_id(0, true);

Longer term, I think it will be good to move away from a hint table and to hints stored in shared memory.

thoughts?

[0] https://github.com/postgres/postgres/blob/master/src/backend/utils/activity/backend_status.c#L679-L755

--
Sami Imseih
Amazon Web Services (AWS)