| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | chong(dot)peng(at)enmotech(dot)com, Lukas Fittl <lukas(at)fittl(dot)com> |
| Subject: | Fix pg_stat_statements losing normalized query text after reset |
| Date: | 2026-08-31 07:07:47 |
| Message-ID: | 29410A4B-9E7B-4557-A289-B8AB6299707F@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-pg_stat_statements-preserve-normalized-query-text.patch | application/octet-stream | 14.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Gustafsson | 2026-08-31 07:16:52 | Re: Offline data checksum changes can cause incorrect checksum state on standbys |
| Previous Message | Xuneng Zhou | 2026-08-31 07:05:12 | Re: timeout value overflow in wait for lsn |