Thread

Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE

Michael Paquier <michael@paquier.xyz> — 2025-12-29T00:52:07Z

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