Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.1\))
Subject: Re: Cancel problems of query to pg_stat_statements
From: Andrey Borodin <x4mmm@yandex-team.ru>
In-Reply-To: <31431745515032@mail.yandex-team.ru>
Date: Mon, 28 Apr 2025 11:16:19 +0500
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <26A1F7AF-810A-4D60-A6CB-8B749BCCE747@yandex-team.ru>
References: <31431745515032@mail.yandex-team.ru>
To: Roman Khapov <rkhapov@yandex-team.ru>
Archived-At: <https://www.postgresql.org/message-id/26A1F7AF-810A-4D60-A6CB-8B749BCCE747%40yandex-team.ru>
Precedence: bulk


> On 24 Apr 2025, at 22:49, Roman Khapov <rkhapov@yandex-team.ru> wrote:
>=20
> 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.=