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

On Mon, Nov 9, 2026 at 11:45 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Nov 09, 2026 at 10:39:37AM +0800, Xuneng Zhou wrote:
> > 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?
>
> Hmm, no. I had something less complicated in mind, as of the
> patch attached.
>
> Note also that I have changed the SQL function to use a integer for
> the restart time, defaulting to -1 to map with the never-restart.
> Passing down a restart time of 0 also makes the test faster, as the
> worker is restarted without a delay.
>
> So the attached patch removes the need for a flag, and just relies on
> a plain kill(). Once we do that, the restart time gets effective when
> set.

That's simpler and the code LGTM. My AI raised two small issues for
the test and doc that seems deserve a comment.

1) The assumption that a new process must have a new PID could be unsafe.

The OS could reuse the exited and reaped PID number and assign it to
the new process, which is possible on Windows. So the test would fail
even if restart occurs.

+$result = $node->poll_query_until(
+ 'postgres',
+ qq[SELECT count(*) FROM pg_stat_activity
+ WHERE backend_type = 'worker_spi dynamic' AND pid <> $pid
+ AND wait_event = 'WorkerSpiMain';],
+ '1');

2) The doc change implies that a set bgw_restart_time guarantees
restart. But if the worker exits with status 0, the postmaster would
unregister and not restart it.

+ The worker is then restarted based on
+ <structfield>bgw_restart_time</structfield>, if set.
Requires both <literal>BGWORKER_SHMEM_ACCESS</literal> and

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