Fix pg_stat_statements losing normalized query text after reset

Started by Chao Li20 days ago2 messageshackers
Beta feature

Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.

appliessuccessCI history

You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:

docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253616
psql -h localhost -U postgres

Built from patchset v1 (message #1), September 20, 2026 at 06:49 AM.

Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:

git clone --branch t253616_1 https://github.com/hackorum-dev/postgres.git

In a checkout you already have, add the fork once:

git remote add hackorum https://github.com/hackorum-dev/postgres.git

then, for this patchset and every later one:

git fetch hackorum t253616_1 && git checkout t253616_1

Patchset v1 (message #1) is on t253616_1

Jump to latest
#1Chao Li
li.evan.chao@gmail.com

Hi,

I received this potential bug report from Chong Peng, who is in the CC list. PG10 introduced a feature:
```
• Show ignored constants as $N rather than ? in pg_stat_statements
```

The behavior is like:
```
evantest=# SELECT pg_stat_statements_reset();
pg_stat_statements_reset
-------------------------------
2026-08-31 10:14:17.326521+08
(1 row)

evantest=# SELECT oid FROM pg_class WHERE relname in ($1,$2,$3) \parse stmt1

evantest=# \bind_named stmt1 'not_exist_tab1' 'not_exist_tab2' 'pg_class' \g
oid
------
1259
(1 row)

evantest=# SELECT queryid,query FROM pg_stat_statements;
queryid | query
----------------------+-----------------------------------------------------------
-6242574738340340947 | SELECT oid FROM pg_class WHERE relname in ($1 /*, ... */)
7506118397046287281 | SELECT pg_stat_statements_reset()
(2 rows)
```

In the pg_stat_statements output, it shows “$1 /*, …*/“ rather than “$1, $2, $3”.

But the problem is that, after pg_stat_statements_reset(), the display becomes “$1, $2, $3”:
```
evantest=# SELECT pg_stat_statements_reset();
pg_stat_statements_reset
-------------------------------
2026-08-31 10:16:43.419045+08
(1 row)

evantest=# SELECT queryid,query FROM pg_stat_statements;
queryid | query
---------------------+-----------------------------------
7506118397046287281 | SELECT pg_stat_statements_reset()
(1 row)

evantest=# \bind_named stmt1 'pg_class' 'pg_attribute' 'pg_index' \g
oid
------
1249
1259
2610
(3 rows)

evantest=# SELECT queryid,query FROM pg_stat_statements;
queryid | query
----------------------+------------------------------------------------------
-6242574738340340947 | SELECT oid FROM pg_class WHERE relname in ($1,$2,$3)
7506118397046287281 | SELECT pg_stat_statements_reset()
-3049491949917800185 | SELECT queryid,query FROM pg_stat_statements
(3 rows)
```

From my debugging, I think the problem is that entry_reset() deletes the entry from pgss_hash via SINGLE_ENTRY_RESET, while the prepared statement itself remains cached and can be executed again without going through parse analysis.

When bind_named executes the prepared statement again, pgss_ExecutorEnd() calls pgss_store(). Since the original entry has already been removed from pgss_hash, pgss_store() has to create a new entry. At this point there is no JumbleState available to regenerate the normalized query text, so the entry is recreated using the original query string, and the squashed representation is lost.

The question is whether pg_stat_statements_reset() really needs to delete the hash entry in this case? It feels to me that, as long as a prepared statement remains cached and can still be executed without being parsed again, it might be reasonable for pgss to retain the existing hash entry, particularly its representative normalized query text, while resetting its statistics.

The currently executing pg_stat_statements_reset() statement itself is an exception. Its old entry needs to be removed so that the current invocation can establish a fresh entry after the reset.
There is another case to consider: after a reset, the same query ID might be parsed again with a different representative query text. In that case, the retained sticky entry should be updated with the newly normalized query text, rather than continuing to use the text retained from before the reset.

Based on this understanding, I made a fix that resets existing entries back to the sticky state rather than deleting them. This preserves the normalized query text for cached prepared statements. If a query with the same query ID is parsed again later, the sticky entry is updated with the newly normalized query text. I’m not very familiar with the history and design considerations around pg_stat_statements_reset(), so I don’t have full confidence that retaining the entries is the intended approach, so comments are welcome.

See the attached patch for details.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachments:

t253616_1
v1-0001-pg_stat_statements-preserve-normalized-query-text.patchapplication/octet-stream; name=v1-0001-pg_stat_statements-preserve-normalized-query-text.patch; x-unix-mode=0644Download+269-38
#2Michael Paquier
michael@paquier.xyz
In reply to: Chao Li (#1)
Re: Fix pg_stat_statements losing normalized query text after reset

On Mon, Aug 31, 2026 at 03:07:47PM +0800, Chao Li wrote:

Based on this understanding, I made a fix that resets existing
entries back to the sticky state rather than deleting them. This
preserves the normalized query text for cached prepared
statements. If a query with the same query ID is parsed again later,
the sticky entry is updated with the newly normalized query
text. I’m not very familiar with the history and design
considerations around pg_stat_statements_reset(), so I don’t have
full confidence that retaining the entries is the intended approach,
so comments are welcome.

I doubt that this is worth complicating the code of PGSS for.
pg_stat_statements_reset() is used, sure, but the case of a prepared
statement reusing a non-normalized query post-reset feels too edgy for
me to care about. Let's keep the code simpler.
--
Michael