Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

Hi Michael,

On Mon, Nov 9, 2026 at 7:05 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Sat, Nov 07, 2026 at 11:22:35PM +0800, Xuneng Zhou wrote:
> > On Sat, Nov 7, 2026 at 9:05 PM Xuneng Zhou <xunengzhou(at)gmail(dot)com> wrote:
> >> On Sat, Nov 7, 2026 at 4:26 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> >>> Yes, that's why I went with the interrupted flag. I can attach a
> >>> version that terminates them directly instead, if that is acceptable.
> >>> My logic was that if this gets committed before the 19 release, the
> >>> additional flag shouldn't be an issue and it's a better solution.

Thanks for sharing your thoughts.

> On the contrary, it looks confusing to me to have two flags that cover
> the same meaning on the backend side: decide if an interruptible
> bgworker should be stopped or not.

I am also unsure whether adding a new flag could be a 'better'
solution. It keeps the current behavior of the postmaster as mediator
and avoids the potential pid reuse issue, at the cost of additional
state management.

> TerminateBackgroundWorkersForDatabase() is new code as of v19, so
> sticking a different convention before release if we can get a
> slightly better behavior (here your argument being a better handling
> of the restart time, if defined for a worker), sounds fine to me.
> There is always the argument that even if a worker is stopped due to a
> database command the bgworker could reconnect to a different database
> if the so-said connection database is under a GUC whose value that can
> be refreshed with a SIGHUP. (If I get it right that your second
> suggestion is just to touch ForDatabase() and have a kill() bundled
> into it to match with the autovac behavior).

That's what I've in mind. My original thought is to add worker
selection and terminatiion into the existing CountOtherDBBackends
scan. That avoids the separate worker-slot scan and PID lookups in
ForDatabase(), at the cost of adding and initializing a new marker in
PGPROC like PROC_IS_BGWORKER_INTERRUPTIBLE. Is this sensible/doable?

> > > I don't know. We could either follow the convention or break with it.
> > > There are some discussions & reports related to PID reuse race
> > > [1][2][3][4]. Let's wait and see Michael and others' thoughts on this.
> > >
> > > [1] https://www.postgresql.org/message-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA%40mail.gmail.com
> > > [2] https://www.postgresql.org/message-id/CAAZKuFaz7xhiOmUgrYXPEvNLe3aT8y2xpCVVDLQ98SU_4OE76g%40mail.gmail.com
> > > [3] https://www.postgresql.org/message-id/flat/20230208012852.bvkn2am4h4iqjogq%40awork3.anarazel.de
> > > [4] https://www.postgresql.org/message-id/3cf7148a-be17-4fff-9447-a2530590ccd0%40gmail.com
> >
> > An interesting experiment to show that the reuse is real on windows
> > [1]. But our failure would require timing more than mere PID reuse and
> > we don't have over 100k machines to run simultaneously...
>
> I'm not really planning to worry about that when it comes to the
> handling of the workers, and even more in terms of making them
> interruptible on an option basis. Anyway, I don't think we'll ever be
> able to guarantee that for example a PID given in input of
> pg_terminate_backend() or pg_cancel_backend() will be exactly the
> process you are targeting, because there will always be a delay
> between the moment a client sends the request and the moment the
> request is actually executed by the server. So I'd rather keep it
> simple.

Yeah, that seems unavoidable. My read from the prior discussions &
reports is that the reuse in unix systems is not a thing to worry
about thanks to its sequential reusing policy. I don't see a
discussion of that issue on windows and there're reports for that. To
avoid the race to some extent, we could somehow add a recheck before
the signaling as Amit suggested before. Not sure of the necessity of
that.

--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.