Feedback-ID: i0fe9450f:Fastmail
Date: Sat, 21 Mar 2026 11:46:23 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Lakhin <exclusion@gmail.com>,
	=?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>
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP
 DATABASE
Message-ID: <ab4Gf2r-Bd6P7BiW@paquier.xyz>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="KMNwu+aFFu8hPoZK"
Content-Disposition: inline
In-Reply-To: <abtJLEAsf1HZXWdR@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/ab4Gf2r-Bd6P7BiW%40paquier.xyz>
Precedence: bulk


--KMNwu+aFFu8hPoZK
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 19, 2026 at 09:54:04AM +0900, Michael Paquier wrote:
> For now I am planning for the attached to get more information from
> widowbird, which should take a few days at worst.  That would make
> clear if we have a timing issue with the requests sent to the
> postmaster.  Launching the queries for worker_spi_launch() and
> pg_stat_activity on the database postgres may also improve things, but
> I don't really buy it, even if I may be wrong.

A couple of days later after 79a5911fe65b, widowbird seems to have
cooled down a bit:
https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=3Dwidowbird&br=
=3Dmaster

The extra debugging information is proving to be useful.  For example,
looking at this one for the RENAME TO case:
https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=3Dwidowbird&d=
t=3D2026-03-19%2003%3A35%3A03&stg=3Dmodule-worker_spi-check

And some problematic pattern logs:
2026-03-19 04:49:07.848 UTC [271557:4] 002_worker_terminate.pl LOG:
statement: ALTER DATABASE testdb RENAME TO renameddb =20
2026-03-19 04:49:07.849 UTC [271557:5] 002_worker_terminate.pl DEBUG:
attempting worker termination for database 16413 =20
2026-03-19 04:49:07.849 UTC [271557:6] 002_worker_terminate.pl DEBUG:
termination requested for worker (PID 271553) on database 16413
[...]
2026-03-19 04:49:08.732 UTC [271557:19] 002_worker_terminate.pl DEBUG:
attempting worker termination for database 16413 =20
2026-03-19 04:49:08.732 UTC [271557:20] 002_worker_terminate.pl DEBUG:
termination requested for worker (PID 271553) on database 16413 =20

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.
--
Michael

--KMNwu+aFFu8hPoZK
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmm+Bn8ACgkQnvQgOdby
QH2wqg/+N9+jf6YWU0mkDroPW2A8Q4zc7SR1Zlym0jGeOz4g20UdXUEfEqrfSErP
ftXna81uJOpzYlksupeVvlLg8Nbn1ntGsTIytvScXeA/7g2L3gBcoA1Nucld76TQ
vEOjddMyPo7ghkOJ82Q/E05n4gaSFUzM1MJpap3Qo55wHAc/AaxtcDXE8h4MGKZ3
aAC+GVnL/ZPtIWFgcL61IOQq5/fPxn0a5kBeaNIRE9vV2Kw1MTCglYlZMxw+/6TW
1Tlls9FzIxlM6NFw9+HWHhMAtyY9tnM/9Pt5iru+AJhkqtwhIqY7A8XKWh9GDYNR
L/7YfaXy6/NAHKgPTd9qi5yui+mZNp+Q5/HbdgBbUbmEvE8hhePKpXVO7mIndmEH
ZL1JxksVM9Fyo7yJVDbSAumfk5pS4/Fo959NVZj4cbnvoNHs2ImQxquNoBH/8AvN
5lgjnYrnUbwGJS4fRsyDikK/HPjERQRUFm9GYjRP5BQMMj5FzXUU1RqrOQFixBD5
JbrFD3vRiFsZeyDtQkKYK+S9yVz5JKGjgODe0gqOgwmOdSrLvGIJMX+2fIKOsHRa
kSG366b+pq0El3SPkdFADzlyuS/hKFSePW1OGPJIJvqpbtKxWscI/cNWuUYOIC4I
bpywgLqhBysYeLG3INuvxO9fK6ZCxs0cHFHvh9pSCqYntEitTVs=
=vrzi
-----END PGP SIGNATURE-----

--KMNwu+aFFu8hPoZK--