Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 29 Dec 2025 09:52:07 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: "Ryo Matsumura (Fujitsu)" <matsumura.ryo@fujitsu.com>,
	"Aya Iwata (Fujitsu)" <iwata.aya@fujitsu.com>,
	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: <aVHQt-asgKx5dnap@paquier.xyz>
References: 
 <CAFj8pRB_FH-Pcth9XFcpY2OTasVOP-2DfYOsVxL38igw0O4hdg@mail.gmail.com>
 <OS7PR01MB119647141272DBF9364A1C5AAEAADA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRCRnN4SyZDPbQwTyKZ_kVHfwLG6udCXsXDTG_z9fWwYQg@mail.gmail.com>
 <OS7PR01MB119648203BD748ED008FA5BF5EAABA@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRCn0=jn4yaeg1JoxxxnUNeXm1KCouES8Puq_GgBsXrNTQ@mail.gmail.com>
 <OS7PR01MB1196405302B700736580DEE49EAA8A@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <CAFj8pRBk7zTKoxAAKAihNQuimztniWHPyRWHKp2iHq_2hH+dvQ@mail.gmail.com>
 <OS7PR01MB11964C83E8E543743581250B0EAB3A@OS7PR01MB11964.jpnprd01.prod.outlook.com>
 <TYCPR01MB1131647E781856780072FBAE9E8B0A@TYCPR01MB11316.jpnprd01.prod.outlook.com>
 <CAFj8pRBtApX5WGMOzz4+_xe3ZvBLc6BsrUV13KTeNpAL6ZxFzA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="/YPpcdT7OzpSi0QS"
Content-Disposition: inline
In-Reply-To: 
 <CAFj8pRBtApX5WGMOzz4+_xe3ZvBLc6BsrUV13KTeNpAL6ZxFzA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aVHQt-asgKx5dnap%40paquier.xyz>
Precedence: bulk


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

On Fri, Dec 26, 2025 at 12:04:47PM +0100, Pavel Stehule wrote:
> I am not sure if I understand what you prefer.

Seems to me that Iwata-san sides with compatibility.  Spoiler alert: I
do side with compatibility.  See below for more details.

> 1. When bgworker is marked as INTERRUPTIBLE, then can be cancelled
> immediately - there is full agreement - I don't see any problem - just
> probably almost all bgworkers will be not be marked as INTERRUPTIBLE

Yes, these can and should be stopped.

> 2.  When bgworker is not marked as INTERRUPTIBLE, then can it be cancelled?
> How dangerous is this? If it cannot be cancelled, then ALTER should wait on
> lock forever, or there should be some special timeout - like CREATE, DROP
> DATABASE and waiting on template databases.

Cancellation is a different thing.  bgworkers can be tweaked to answer
to specific signals with their own handlers.  What we are discussing
here is if a signal should be sent to a bgworker or not when
performing specific operations.

> 3. If the user needs to execute ALTER, then probably he manually terminates
> the worker any time. Are currently used workers safe against terminating?
> We know so mostly workers will be restarted after timeout - and then it is
> "safe".  It is really safe? Can we expect it? If it is safe, then we can
> implement FORCE clause. Probably any worker can be restarted - and without
> safety it is hard to imagine using in the production.

That would be up to an extension developer.  The new behavior can be
useful in some cases.  Forcing it by default could lead to unwanted
results.

> I think there is a fundamental question that should be solved before - what
> is a risk of canceling bgworker? The possible reply is so there is not any
> risk for correctly implemented bgworkers. And then there is a question if
> we still need the flag INTERRUPTIBLE? My opinion for this case is probably
> yes, because cancelling can introduce some bigger latency.

I can think about two reasons at the top of my mind, at least:
1) Making the background workers non-interruptible is the default
behavior that we have since 9.3.  Changing the default by allowin
these to be interrupted could lead to a silent behavior change that
extension developers are not aware of because the bgworker lubrary
code would still be compiled as-is, and would still be able to start
correctly.  This links to my second point.
2) Operator error, and these tend to happen a lot.  Somebody with the
right to create a database could decide to use as template a database
that a bgworker is linked to, leading to failures with operations
background workers expect to be able to achieve while online if we
change the default, because it does things that are critical and
should not be interrupted (one could compare that to what an
autovacuum worker does with an antiwraparound work, perhaps, which
cannot be interrupted).

As a whole, I think that we have more advantages in keeping the
default, making the possibility to make a bgworker interruptible an
opt-in choice is extra cream that can be added on top of a cake, and
one is free to add the extra cream to their portion of the cake.
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmlR0LcACgkQnvQgOdby
QH2vkg//Vi2cNR9smhtqJvh3jeOpJErN+UuIMcm+WVGqv4qEVq6zWIl7NZTibJU0
UMYaIEIl2hoRHqRKHw+Z6Dpk1YD0ymoOi61ggKUY0viznzIrMYp5AVyGp1mdd4NV
JSUd+uFKyYDmr8TcyyKjS4VbDdyjvUdEbuDtOBx92emspqcK/NnJ83l0X3d+VMvA
vdFj1x2rkB/cAqQNu1wU+zYKCvOQyR4kIroO325OjH12mSUkvdhKuKKDmXDMKGij
Tyh2Wy36dGdOBKnfB4uTO2d2bsRgyNRRBjmNn4FvIYnEtdE7e7BbFjtFn3I4zbvm
f9LY9aYW4Hk01zjFiTl48Z9XBf7i9ccMiw6yNAQj6/TzJ1hjWT2Di/AVJ23VMVgs
FB3wEf5Eg9EO16Oh6VG3+SVNB4qqPK8MMQwOryZbml7YhwG7f21bYyUWFuvEEGij
8lv398FkU7qH1pnBWWyHDvr44AEcbQau3Qpm7OR2FQtGupmbgqUarhaI8S5GpXKN
sn/qKS9MOYlh6SXRaPwqXX4dwudrA/hGDrf1WpuOt+AZ7Fc4nKxnGc6dDi/Aiu8F
upDrp1Bi/jfcm8Pv1Fubr8NtBFvBnUghxdtH/LS26q8YmqkXSBWpjyex7SH0iuse
/ozZ1sRP0ySF3g5Z/Ek3ol22UjKzCiohIc9iCZyFOmAGmWTbRwg=
=IuB1
-----END PGP SIGNATURE-----

--/YPpcdT7OzpSi0QS--