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

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.

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.

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).

> > 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. There may be something with threading here, actually, we've
mentioned that as we could not rely on PIDs anymore, should the switch
to threads happen, then a concept of session number would be required.
If it is 8-byte wide, the PID rotation concern is moot.
--
Michael