Cancel problems of query to pg_stat_statements
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.
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:t51493psql -h localhost -U postgresBuilt from patchset v3 (message #3), July 27, 2026 at 09:13 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 t51493_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t51493_3 && git checkout t51493_3Patchset v3 (message #3) is on t51493_3
Attachments:
cancelable-qtext_load_file.patchtext/x-diff; name=cancelable-qtext_load_file.patchDownload+3-2
On 24 Apr 2025, at 22:49, Roman Khapov <rkhapov@yandex-team.ru> wrote:
Hi!
Recently we faced a problem in out production psql installation, which was that we had to cancel all requests to the db, including performance monitoring requests, that uses ps_stat_statements. But we could not cancel the request in usual way, and had to kill -9 the pg process of it.
Interesting problem, thanks for raising it!
We've noticed that the the query execution stuck on PGSS_TEXT_FILE file reading in function qtext_load_file, which doesn't have CHECK_FOR_INTERRUPTS in the read cycle. In addition to our case with large PGSS_TEXT_FILE (and maybe the problems with virtual disk i/o) that can explain uncancellable pg_stat_statements queries.
I'm afraid it might be not so easy to add CHECK_FOR_INTERRUPTS there. Most probably you was holding a LWLockAcquire(pgss->lock, LW_SHARED) somewhere (do you have a backtrace?), which prevent interrupts anyway.
Thanks!
Best regards, Andrey Borodin.
Hi! Thanks for the review!
Its true that qtext_load_file() can be called with acquired lock in pg_stat_statements_internal()…
So I perform INTERRUPTS_PENDING_CONDITION in v2 patch and call CHECK_FOR_INTERRUPTS later, after cycle ended and lock released.
On Mon, May 12, 2025 at 03:48:29PM +0500, Roman Khapov wrote:
Its true that qtext_load_file() can be called with acquired lock in
pg_stat_statements_internal()...
So I perform INTERRUPTS_PENDING_CONDITION in v2 patch and call
CHECK_FOR_INTERRUPTS later, after cycle ended and lock released.
This patch is only a workaround for a larger problem: the PGSS text
file does not and will never scale once it reaches a large size and
once we have a high turnover rate of the PGSS entries due to different
query IDs.
FWIW, Sami Imseih has mentioned to me a few days ago that the state of
the pgstats API (that can be used for custom stats kinds as well) was
basically in a shape good enough to move PGSS to pgstats as a custom
kind, and we should be able to move the query text file to be entirely
in-memory. This proposal will hopefully materialize at the beginning
of the v20 cycle.
--
Michael