Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 16 Oct 2025 12:54:31 +0900
From: Michael Paquier <michael@paquier.xyz>
To: "Aya Iwata (Fujitsu)" <iwata.aya@fujitsu.com>
Cc: 'Peter Smith' <smithpb2250@gmail.com>, Chao Li <li.evan.chao@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP
 DATABASE
Message-ID: <aPBsd2e2SqLs0Kdk@paquier.xyz>
References: <aOctHMnSZcREh0BR@paquier.xyz>
 <OSCPR01MB1496614832F8014EC16FB78D2F5EEA@OSCPR01MB14966.jpnprd01.prod.outlook.com>
 <aOc7_JGErxvqBLMP@paquier.xyz>
 <OSCPR01MB14966EC12277712131EB8EDF1F5EEA@OSCPR01MB14966.jpnprd01.prod.outlook.com>
 <OS7PR01MB11964C8FE9CDCC0F4C9110988EAEEA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <E8C35CCE-E92F-467B-A604-5C1BBBA8F1C8@gmail.com>
 <OS7PR01MB11964C077A3E61F4887DD3D09EAEFA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAHut+PtbOP_80OPZXCUZO=-pBJSRTmHcQ2MnVTFov1meNbw18Q@mail.gmail.com>
 <CAHut+Pt5BN0LDh7OzbNqh9+zqHBgsrLX+vh-gn+3FKYTFHMvhw@mail.gmail.com>
 <TY3PR01MB11969CBD6DF3E0DB820AD4262EAE8A@TY3PR01MB11969.jpnprd01.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="VgLTlR48wNPj/NbY"
Content-Disposition: inline
In-Reply-To: 
 <TY3PR01MB11969CBD6DF3E0DB820AD4262EAE8A@TY3PR01MB11969.jpnprd01.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aPBsd2e2SqLs0Kdk%40paquier.xyz>
Precedence: bulk


--VgLTlR48wNPj/NbY
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Oct 15, 2025 at 02:48:43AM +0000, Aya Iwata (Fujitsu) wrote:
> Thank you for your comments. I updated this patch to v0007.

+ * Exit the bgworker when its database is dropped, renamed, moved to a
+ * different tablespace, or used as a template for CREATE DATABASE.

I don't think that we need to list all these operations in details
here. We could just say "if its database is involved in a CREATE,
ALTER or DROP database command".  The docs should provide these
details, of course.

+#define BGWORKER_EXIT_AT_DATABASE_CHANGE       0x0004=20

Flag name works here.

# XXX This spends more than 5 seconds because the backend retries counting
# number of connecting processes 50 times. See CountOtherDBBackends().

And that's annoying.  Let's activate what I call the cheat mode for
this one: an injection point that, if defined, enforces a lower number
of tries when we loop over the workers to stop.  That would make the
test much faster when using a worker that should not be stopped,
without impacting the coverage.

I suspect that your new test 002_worker_terminate.pl has a race
condition in run_db_command(): are you sure that the bgworker has
enough time to be reported as stopped in the server logs once
safe_psql() finishes to run the database command given by the caller?
On very slow and/or loaded machines, particularly, that could hurt the
stability.  It seems to me that this should use a wait_for_log()
instead of a log_contains(), waiting for the worker to be reported as
stopped depending on the command executed.

Shouldn't this test also check that worker 0 (the one that does not
have the flag set) is still running at the end of the test?  I assume
that querying pg_stat_activity would be enough at the end of the
script.
--
Michael

--VgLTlR48wNPj/NbY
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmjwbHcACgkQnvQgOdby
QH1tjw//RCSZr5jWcf8mIcA5rxa1arzaINEHJXzr2mKcS71PlrxN4tK6IQWQEtDa
xc/2oXm4NRg3zD9TMV4TSUaDaMKyUYhgRN8t2i7Nug3uO9sF+IrmFb2ys/rSMSvE
s2cSuzfU15KVDC6wcLFkEy7gs1S3bQXF61+45voXdDUIcBjsUswKAVmLLFgkYXC8
oJQ24Fq3Wkma/5m2L9y2QF9s72EHuO+nHb0cgVefM/FHyEqGXMKbW/wtpaB5QnTk
yYFgxjtnrR93Uo+PTl7LU+X7xkavGUnlgg0OuB/4FCYg5yzJ2s6Cv34ZF9LReAZk
ial9LFNJkmkJvMW5GyPEBebjXCzDbHY7RkPug2gl/rB37/P0smZVMyckDxrXiamQ
SOa1E09OfZkQn0oMnri1Ma5wmZs4nmL8wq+Sx9AuShaNKmOb2Or8ZDoe+tfCxULp
VDy+z+hECBeExZcv1KCQb8WKGtK4rOUSJQJBlTUrM/dvomcuAEo03mKmw75ZmGWY
7c0gr7bfOa0Dx1bj/E+Xz2FqLpz9bCu5dOhJanT9fMWpZj2Vsk4Bzs2xg7IDmTJA
kfg0pZbuEmnUx1BZArZRlTWuWEN/5rfTuwkz2Cg96rA/uo0BmprgKuzrnfIazzdr
LXqiR8DGfvcyw8O4AtW/dPYr14kRRsBuixtMiM9SCWYuj/KQATM=
=XH8N
-----END PGP SIGNATURE-----

--VgLTlR48wNPj/NbY--