Обсуждение: Re: Cancel problems of query to pg_stat_statements

> 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
usualway, 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'thave CHECK_FOR_INTERRUPTS in the read cycle. In addition to our case with large PGSS_TEXT_FILE (and maybe the
problemswith 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.

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