Feedback-ID: i0fe9450f:Fastmail
Date: Sun, 5 Apr 2026 20:47:30 +0900
From: Michael Paquier <michael@paquier.xyz>
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>, Alexander Lakhin <exclusion@gmail.com>,
	"Aya Iwata (Fujitsu)" <iwata.aya@fujitsu.com>,
	Peter Smith <smithpb2250@gmail.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: <adJL0oaUR4r2bRLu@paquier.xyz>
References: <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>
 <f913fba1-da59-404c-9eb3-07c7304be637@gmail.com>
 <acun5kN_JaLFcfq_@paquier.xyz>
 <fb4028b6-e07f-4d84-a65e-c90bc96e6356@gmail.com>
 <3074140.1775074810@sss.pgh.pa.us>
 <OS9PR01MB12149DEAB9D2FAF32C1E8079DF551A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="ICwJG/7E/hj+H294"
Content-Disposition: inline
In-Reply-To: 
 <OS9PR01MB12149DEAB9D2FAF32C1E8079DF551A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/adJL0oaUR4r2bRLu%40paquier.xyz>
Precedence: bulk


--ICwJG/7E/hj+H294
Content-Type: multipart/mixed; boundary="5IR7sT7khOVxhAKs"
Content-Disposition: inline


--5IR7sT7khOVxhAKs
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Apr 02, 2026 at 03:20:59AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Thanks for paying attention the thread.
> I've been considering why the XLogFileClose() is slow only on widowbird but I
> have no idea. But on this thread, I think we can just put a workaround for
> stabilization.

I am not convinced that we have a need for an environment variable for
this case, TBH.  It would be enough to make sure that the spawned
worker is paused on its WaitLatch(), with a naptime large enough to
allow the initial transaction to commit, as in the attached.  When the
interruption is run, the worker would stop at CHECK_FOR_INTERRUPTS(),
before beginning a new transaction.
--
Michael

--5IR7sT7khOVxhAKs
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=0001-Improve-stability-of-worker_spi-termination-test.patch
Content-Transfer-Encoding: quoted-printable

=46rom fe0a2284d87930cd18d13ff4c473f96de5a965e8 Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
Date: Sun, 5 Apr 2026 20:45:16 +0900
Subject: [PATCH] Improve stability of worker_spi termination test

This switches the test to wait for the spawned bgworkers to reach their
main loops, napping.  worker_spi.naptime is enlarged to 10 minutes to
give enough room for slow machines to reach this state.
---
 .../worker_spi/t/002_worker_terminate.pl        | 17 +++++++++--------
 1 file changed, 9 insertions(+), 8 deletions(-)

diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/te=
st/modules/worker_spi/t/002_worker_terminate.pl
index b0e6a5376d4c..a7d507cc0596 100644
--- a/src/test/modules/worker_spi/t/002_worker_terminate.pl
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -20,7 +20,6 @@ if ($ENV{enable_injection_points} ne 'yes')
 sub launch_bgworker
 {
 	my ($node, $database, $testcase, $interruptible) =3D @_;
-	my $offset =3D -s $node->logfile;
=20
 	# Launch a background worker on the given database.
 	my $pid =3D $node->safe_psql(
@@ -28,13 +27,11 @@ sub launch_bgworker
         SELECT worker_spi_launch($testcase, '$database'::regdatabase, 0, '=
{}', $interruptible);
     ));
=20
-	# Check that the bgworker is initialized.
-	$node->wait_for_log(
-		qr/LOG: .*worker_spi dynamic worker $testcase initialized with .*\..*/,
-		$offset);
-	my $result =3D $node->safe_psql('postgres',
-		"SELECT count(*) > 0 FROM pg_stat_activity WHERE pid =3D $pid;");
-	is($result, 't', "dynamic bgworker $testcase launched");
+	# Check that the bgworker is initialized and napping.
+	my $result =3D $node->poll_query_until('postgres',
+		qq[SELECT wait_event FROM pg_stat_activity WHERE pid =3D $pid;],
+		qq[WorkerSpiMain]);
+	is($result, 1, "dynamic bgworker $testcase launched");
=20
 	return $pid;
 }
@@ -64,11 +61,15 @@ sub run_bgworker_interruptible_test
=20
 my $node =3D PostgreSQL::Test::Cluster->new('mynode');
 $node->init;
+# The naptime is large enough to give room on slow machines to reach
+# the main loop of the spawned workers, after these initialize their
+# schemas.
 $node->append_conf(
 	"postgresql.conf", qq(
 autovacuum =3D off
 debug_parallel_query =3D off
 log_min_messages =3D debug1
+worker_spi.naptime =3D 600
 ));
 $node->start;
=20
--=20
2.53.0


--5IR7sT7khOVxhAKs--

--ICwJG/7E/hj+H294
Content-Type: application/pgp-signature; name=signature.asc

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

iQIyBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmnSS9IACgkQnvQgOdby
QH01qQ/4iDlT1Gid5yUsJrolY9dzTekI/pbSg3Xb6Iz2B7JsQYLOB30+m+q+sXnn
FZx5bk9gP1e7x1D8BHYSnNxdh4Ai/wZVFMQFbbEVNYwUVS8d56JaIelx58f1jWcG
peqsGqkq2+YFvNIO+r2eKqAIss0LrgjJ/sYy3CDi7+ceg0hUV3lIbDh6nvfQ4hgh
JcwOiE6DWhfdNDG0ce1Bphj6nn8NreBrVezwVk7id3L5+9ps9JMVxJS9HYxQITzE
Tn0mz5LGIqAt7Bxc7ErE5MNv278Tko8F90Yx288q2o0AxfiwFM6OTK3fczUx1gas
wZ/H+iEms0co1Xh+a0Om952xfp6uqiiw6dh9pnKA3M/VK96EJ2wdh9IKAMnrUfr2
igHO9YaWLBYb8lnsjpwRXhRuMZMm6z8MBVBDgRq+bcz4l6Gz0/pk9Vin10VbwXLr
2DE4AhJ+3W8wXHbIXoSGXJnvaRL0kVpqXIskC+uuCe2/YqYUbD29BfeQV9eMOXea
Z1MGjs5HTH0YyvNVbeaKZGPDklhPAYZCiHbS0sMMssCMBmwRPAngM4kz/UzZxEoy
CH2l5eEpPWbDqCiJBOvC+s5Dea0YJTQ4a+xpVgab4rhvL/UJcZV989WW+959O+Cs
v68/vyFQV1kJ+Le8H2ufxqYj3ZqOjKGNE09NPAmnkgQOtlBqcw==
=Z84b
-----END PGP SIGNATURE-----

--ICwJG/7E/hj+H294--