Message-ID: <f913fba1-da59-404c-9eb3-07c7304be637@gmail.com>
Date: Tue, 31 Mar 2026 10:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: Alexander Lakhin <exclusion@gmail.com>
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP
 DATABASE
To: Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?UTF-8?B?SXdhdGEsIEF5YS/lsqnnlLAg5b2p?= <iwata.aya@fujitsu.com>,
 Peter Smith <smithpb2250@gmail.com>,
 =?UTF-8?B?S3Vyb2RhLCBIYXlhdG8v6buS55SwIOmavOS6ug==?=
 <kuroda.hayato@fujitsu.com>, Pavel Stehule <pavel.stehule@gmail.com>,
 Chao Li <li.evan.chao@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <aVyfvgMplnc6aIE2@paquier.xyz>
 <OS3PR01MB8889505E2F3E443CCA4BD72EEA45A@OS3PR01MB8889.jpnprd01.prod.outlook.com>
 <abev_E-SE94zHP0n@paquier.xyz> <abiUQ5MBGG9MDM1h@paquier.xyz>
 <TYRPR01MB12156CA129F54361D19ECAB56F541A@TYRPR01MB12156.jpnprd01.prod.outlook.com>
 <abjRR1bhoa9N8XEa@paquier.xyz>
 <a99bd177-00e3-4512-ba7c-29e180926467@gmail.com>
 <abkR1aod9_dG-PGM@paquier.xyz> <1020519.1773863522@sss.pgh.pa.us>
 <abtJLEAsf1HZXWdR@paquier.xyz> <ab4Gf2r-Bd6P7BiW@paquier.xyz>
Content-Language: en-US
In-Reply-To: <ab4Gf2r-Bd6P7BiW@paquier.xyz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f913fba1-da59-404c-9eb3-07c7304be637%40gmail.com>
Precedence: bulk

Hello Michael,

21.03.2026 04:46, Michael Paquier wrote:
> So we are able to send the requests to the workers, and these can take
> a long time before being processed by the postmaster.  Querying
> directly "postgres" for the worker_spi_launch() and pg_stat_activity
> queries seems to have reduced the friction, with less requests to
> send.   However, I don't think that this is the end of the story, even
> after 79a5911fe65b I have spotted one case of RENAME TO where the
> requests were sent for a bit more than 4s, before the postmaster had
> the idea to catch up.  RENAME TO is the only one that can get slow
> (really no idea why), so I guess that we could always tweak things a
> bit more:
> 1) Extra injection point to increase the timeout (30s or 60s?) and
> give the postmaster more room to proceed the requests.
> 2) Remove this portion of the test, but it would be sad.
>
> I'll keep an eye for more failures, even if the situation is looking
> slightly better.

Having reproduced this locally (running 3 tests in parallel with
ALTER DATABASE RENAME repeated 200 times, on a slow riscv64 machine), I
discovered that in the bad case the worker doesn't reach the main loop in
time (and CHECK_FOR_INTERRUPTS() inside it), because it doesn't get out of
initialize_worker_spi() -> CommitTransactionCommand().

With this modification:
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3752,3 +3752,3 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
          */
-       int                     ntries = 50;
+       int                     ntries = 500;

@@ -3798,3 +3798,6 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
                 if (!found)
+{
+elog(LOG, "!!!CountOtherDBBackends| found no backends, try %d", tries);
                         return false;           /* no conflicting backends, so done */
+}

I can see the following:
... !!!CountOtherDBBackends| found no backends, try 1
# most of the calls (200 of 201) succeeded with try 1, but there are also:
... !!!CountOtherDBBackends| found no backends, try 7
... !!!CountOtherDBBackends| found no backends, try 51
... !!!CountOtherDBBackends| found no backends, try 74
... !!!CountOtherDBBackends| found no backends, try 84

So the backend is not completely stuck, but CommitTransactionCommand()
may take more than 5 seconds under some circumstances (maybe it's worth
investigating which exactly).

Best regards,
Alexander