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

Started by Zsolt Parragi17 days ago26 messageshackers
Jump to latest
#1Zsolt Parragi
zsolt.parragi@percona.com

Hello

"Allow bgworkers to be terminated for database-related commands" added
BGWORKER_INTERRUPTIBLE: for DROP/ALTER/CREATE DATABASE the postmaster
terminates the background worker... and then forgets about it, even
ignoring bgw_restart_time. So a static worker from
shared_preload_libraries, or a dynamic one with a restart time is gone
until the next postmaster restart.

This also happens when the command itself fails. With a static
interruptible worker (bgw_restart_time = 1) connected to db1, and a
regular session also connected to db1:

DROP DATABASE db1;
ERROR: database "db1" is being accessed by other users
DETAIL: There is 1 other session using the database.

db1 still exists, but its worker never comes back. Same for ALTER
DATABASE and CREATE DATABASE ... TEMPLATE.

pg_terminate_backend() on the same worker do get it restarted after 1s.

The attached patch adds an "interrupt" flag to BackgroundWorkerSlot next
to "terminate", so the postmaster only kills them once and then
restarts them later.

With this, after a successful DROP DATABASE a restartable worker now
comes back and keeps failing to connect to the dropped database every
bgw_restart_time. That is the same as what happens to a
non-interruptible worker whose database disappears, and the extension
can handle it.

Attachments:

v1-0001-Fix-restart-of-interruptible-bgworkers-after-data.patchapplication/octet-stream; name=v1-0001-Fix-restart-of-interruptible-bgworkers-after-data.patchDownload+47-7
#2Iwata, Aya
iwata.aya@fujitsu.com
In reply to: Zsolt Parragi (#1)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

Hello Parragi

Thank you for suggesting this improvement.

I agree that if the database remains after the command is executed,
the terminated bgworker should reconnect.

However, in the proposed implementation, the system would attempt to reconnect
multiple times even if the DROP operation succeeds. I think this point requires
further discussion.
If the `DROP DATABASE` command succeeds, almost all worker creators would likely
not want to reconnect. In that case, I believe this should be handled as a core
feature rather than left to an extension.

In the case of the DROP command, I think users would not want to reconnect to
the database regardless of whether it succeeds or fails. This is because I think
users whose DROP attempt failed would simply try the DROP command again.
If the database is going to be deleted anyway, they likely wouldn't want to reconnect.

On the other hand, in the case of ALTER DATABASE,
if the database ID does not change regardless of the command's result, it might
be useful for the bgworker to reconnect.

Regards,
Aya Iwata

#3Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Iwata, Aya (#2)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Fri, 06 Nov 2026, Iwata, Aya <iwata.aya@jp.fujitsu.com> wrote:

In that case, I believe this should be handled as a core
feature rather than left to an extension.

Yes, I agree that this part is questionable, but to me this still looks like the safest choice:

1. it can already be the case in other scenarios, so this is not really new behavior
2. can the core for sure make the assumption that the bgworker will want to reconnect to the same database again?

#4Xuneng Zhou
xunengzhou@gmail.com
In reply to: Zsolt Parragi (#1)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

Hi Zsolt,

On Fri, Nov 6, 2026 at 7:21 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Hello

"Allow bgworkers to be terminated for database-related commands" added
BGWORKER_INTERRUPTIBLE: for DROP/ALTER/CREATE DATABASE the postmaster
terminates the background worker... and then forgets about it, even
ignoring bgw_restart_time. So a static worker from
shared_preload_libraries, or a dynamic one with a restart time is gone
until the next postmaster restart.

This also happens when the command itself fails. With a static
interruptible worker (bgw_restart_time = 1) connected to db1, and a
regular session also connected to db1:

DROP DATABASE db1;
ERROR: database "db1" is being accessed by other users
DETAIL: There is 1 other session using the database.

db1 still exists, but its worker never comes back. Same for ALTER
DATABASE and CREATE DATABASE ... TEMPLATE.

pg_terminate_backend() on the same worker do get it restarted after 1s.

The attached patch adds an "interrupt" flag to BackgroundWorkerSlot next
to "terminate", so the postmaster only kills them once and then
restarts them later.

With this, after a successful DROP DATABASE a restartable worker now
comes back and keeps failing to connect to the dropped database every
bgw_restart_time. That is the same as what happens to a
non-interruptible worker whose database disappears, and the extension
can handle it.

Thanks for raising this.

Could we just send terminating signal to the matching workers, as
CountOtherDBBackends() already did for autovacuum workers? It doesn't touch
their registrations and adhere to the existing exit-status and restart
rules, without adding a new shared flag.

In theory, there seems to be a PID reuse race between finding the workers
and sending the signal. But this risk is accepted and considered as too
rare in practice to bother in pg_signal_backend() and
terminateOtherDBBackends().

Do we need to add it to the open issue list though it might already be on
Michael's radar?
--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.

#5Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Xuneng Zhou (#4)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Sat, 07 Nov 2026, Xuneng Zhou <xunengzhou@gmail.com> wrote:

In theory, there seems to be a PID reuse race between finding the workers
and sending the signal. But this risk is accepted and considered as too
rare in practice to bother in pg_signal_backend() and
terminateOtherDBBackends().

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.

Do we need to add it to the open issue list though it might already be on
Michael's radar?

I included Michael in the thread, but yes, I think it's also a good idea to add it there.

#6Xuneng Zhou
xunengzhou@gmail.com
In reply to: Zsolt Parragi (#5)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Sat, Nov 7, 2026 at 4:26 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

On Sat, 07 Nov 2026, Xuneng Zhou <xunengzhou@gmail.com> wrote:

In theory, there seems to be a PID reuse race between finding the workers
and sending the signal. But this risk is accepted and considered as too
rare in practice to bother in pg_signal_backend() and
terminateOtherDBBackends().

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.

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]: /messages/by-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA@mail.gmail.com

[1]: /messages/by-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA@mail.gmail.com
[2]: /messages/by-id/CAAZKuFaz7xhiOmUgrYXPEvNLe3aT8y2xpCVVDLQ98SU_4OE76g@mail.gmail.com
[3]: /messages/by-id/20230208012852.bvkn2am4h4iqjogq@awork3.anarazel.de
[4]: /messages/by-id/3cf7148a-be17-4fff-9447-a2530590ccd0@gmail.com

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

#7Xuneng Zhou
xunengzhou@gmail.com
In reply to: Xuneng Zhou (#6)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Sat, Nov 7, 2026 at 9:05 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:

On Sat, Nov 7, 2026 at 4:26 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

On Sat, 07 Nov 2026, Xuneng Zhou <xunengzhou@gmail.com> wrote:

In theory, there seems to be a PID reuse race between finding the workers
and sending the signal. But this risk is accepted and considered as too
rare in practice to bother in pg_signal_backend() and
terminateOtherDBBackends().

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.

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] /messages/by-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA@mail.gmail.com
[2] /messages/by-id/CAAZKuFaz7xhiOmUgrYXPEvNLe3aT8y2xpCVVDLQ98SU_4OE76g@mail.gmail.com
[3] /messages/by-id/20230208012852.bvkn2am4h4iqjogq@awork3.anarazel.de
[4] /messages/by-id/3cf7148a-be17-4fff-9447-a2530590ccd0@gmail.com

An interesting experiment to show that the reuse is real on windows
[1]: https://justanothergeek.chdir.org/2020/07/Is-ProcessID-recycling-%EF%B8%8F-on-Windows-over-rated/
we don't have over 100k machines to run simultaneously...

[1]: https://justanothergeek.chdir.org/2020/07/Is-ProcessID-recycling-%EF%B8%8F-on-Windows-over-rated/

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

#8Michael Paquier
michael@paquier.xyz
In reply to: Xuneng Zhou (#7)
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@gmail.com> wrote:

On Sat, Nov 7, 2026 at 4:26 PM Zsolt Parragi <zsolt.parragi@percona.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] /messages/by-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA@mail.gmail.com
[2] /messages/by-id/CAAZKuFaz7xhiOmUgrYXPEvNLe3aT8y2xpCVVDLQ98SU_4OE76g@mail.gmail.com
[3] /messages/by-id/20230208012852.bvkn2am4h4iqjogq@awork3.anarazel.de
[4] /messages/by-id/3cf7148a-be17-4fff-9447-a2530590ccd0@gmail.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

#9Michael Paquier
michael@paquier.xyz
In reply to: Zsolt Parragi (#3)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Fri, Nov 06, 2026 at 05:42:58AM -0800, Zsolt Parragi wrote:

Yes, I agree that this part is questionable, but to me this still
looks like the safest choice:

1. it can already be the case in other scenarios, so this is not
really new behavior
2. can the core for sure make the assumption that the bgworker will
want to reconnect to the same database again?

Hmm. The restart time is settable at will. It sounds like a problem
to me to not give this option to users, at least. I agree with
Iwata-san's point that in most cases, admins would just want to ditch
away interruptible workers. Howerver, I can buy the case of the
connection database being under an updatable GUC, where the GUC is
updated to a new database, until the restart time kicks in to connect
to a new database than the one originally manipulated (say a rename in
a maintenance window, whatever).

So it's a matter of options. Having one more option sounds like a
good thing to me. Or we could make the restart time change only on
HEAD. As this feature is not released yet, it's still more appealing
to just do the change and be done with it.
--
Michael

#10Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#8)
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@paquier.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@gmail.com> wrote:

On Sat, Nov 7, 2026 at 4:26 PM Zsolt Parragi <zsolt.parragi@percona.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] /messages/by-id/CAFj8pRDZK2zdDgL7b3DS4YEFnfaxcMf7PON1eB1wsor2MauJSA@mail.gmail.com
[2] /messages/by-id/CAAZKuFaz7xhiOmUgrYXPEvNLe3aT8y2xpCVVDLQ98SU_4OE76g@mail.gmail.com
[3] /messages/by-id/20230208012852.bvkn2am4h4iqjogq@awork3.anarazel.de
[4] /messages/by-id/3cf7148a-be17-4fff-9447-a2530590ccd0@gmail.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.

#11Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#8)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

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.

I don't follow this well. Does this mean that this is not a problem
after threading?

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

#12Michael Paquier
michael@paquier.xyz
In reply to: Xuneng Zhou (#11)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 09, 2026 at 11:37:22AM +0800, Xuneng Zhou wrote:

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.

I don't follow this well. Does this mean that this is not a problem
after threading?

Under a threading infrastructure, PIDs have no meaning. So, assuming
that a concept of session IDs is used to replace how we internally
track each thread for the SQL interface, and assuming that this is 8
byte wide, then the PID wraparound concerns would be entirely gone.
This relies on three ifs.
--
Michael

#13Michael Paquier
michael@paquier.xyz
In reply to: Xuneng Zhou (#10)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

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

Attachments:

t254581_13
v2-0001-Fix-restart-of-interruptible-bgworkers-after-data.patchtext/plain; charset=us-asciiDownload+44-19
#14Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#12)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 9, 2026 at 11:42 AM Michael Paquier <michael@paquier.xyz> wrote:

On Mon, Nov 09, 2026 at 11:37:22AM +0800, Xuneng Zhou wrote:

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.

I don't follow this well. Does this mean that this is not a problem
after threading?

Under a threading infrastructure, PIDs have no meaning. So, assuming
that a concept of session IDs is used to replace how we internally
track each thread for the SQL interface, and assuming that this is 8
byte wide, then the PID wraparound concerns would be entirely gone.
This relies on three ifs.

Thanks. It's clear to me now.

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

#15Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#13)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 9, 2026 at 11:45 AM Michael Paquier <michael@paquier.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.

#16Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Michael Paquier (#13)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

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.

Thanks, this is basically the same thing I had in mind.

I attached a v3 based with a few changes:

* The docs still said "the postmaster sends a termination signal", which is no longer true.
* Added Xuneng's doc comment in the test
* I also removed the pid <> $pid condition based on Xuneng's comment
* I changed the max_worker_processes back to total_slots, since that seem to be more generally used in these functions
* There was a typo in the commit message ("restarted or nor")

Attachments:

t254581_16
v3-0001-Fix-restart-of-interruptible-bgworkers-after-data.patchtext/x-patchDownload+46-20
#17Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#13)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 9, 2026 at 11:45 AM Michael Paquier <michael@paquier.xyz> wrote:

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.

That's a clever trick. I did not pay much attention to making tests
faster by shortening the interval like this before. I found two
similar test cases for speedup(027_nosuperuser.pl and
033_run_as_table_owner.pl). With the patch of setting
wal_retrieve_retry_interval to 100ms, the combined runtime decreased
from roughly 11.56s to 8s in local runs without parallelism.

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

Attachments:

t254581_17
0001-Speed-up-logical-replication-permission-tests.patchapplication/octet-stream; name=0001-Speed-up-logical-replication-permission-tests.patchDownload+6-1
#18Xuneng Zhou
xunengzhou@gmail.com
In reply to: Zsolt Parragi (#16)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 9, 2026 at 4:46 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

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.

Thanks, this is basically the same thing I had in mind.

I attached a v3 based with a few changes:

* The docs still said "the postmaster sends a termination signal",
which is no longer true.
* Added Xuneng's doc comment in the test
* I also removed the pid <> $pid condition based on Xuneng's comment
* I changed the max_worker_processes back to total_slots, since that
seem to be more generally used in these functions
* There was a typo in the commit message ("restarted or nor")

There seems to be a risk of interpreting the exit of worker & its PID
reuse as non-exit failure here. How about adding a new critera like
process start time for checking that as V4 does.

# Run query and verify that the bgworker with the specified PID has been
# terminated.
sub run_bgworker_interruptible_test
{
my ($node, $command, $testname, $pid) = @_;
my $offset = -s $node->logfile;

...

my $result = $node->safe_psql('postgres',
"SELECT count(*) = 0 FROM pg_stat_activity WHERE pid = $pid;");
is($result, 't', "dynamic bgworker stopped for $testname");
}

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

Attachments:

t254581_18
v4-0001-Fix-restart-of-interruptible-bgworkers-after-database-commands.patchapplication/octet-stream; name=v4-0001-Fix-restart-of-interruptible-bgworkers-after-database-commands.patchDownload+56-21
#19Michael Paquier
michael@paquier.xyz
In reply to: Xuneng Zhou (#18)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Mon, Nov 09, 2026 at 06:49:41PM +0800, Xuneng Zhou wrote:

There seems to be a risk of interpreting the exit of worker & its PID
reuse as non-exit failure here. How about adding a new critera like
process start time for checking that as V4 does.

I'm finding this addition utterly esoteric, because this would be hit
yes, but the risk is dramatically low compared to making the test more
complex to parse. v3 as sent by Zsolt is just but fine to me.

+  The old worker is known to have exited, so do not
+# rely on the PID of the new worker being different, as it could be reused.

This last sentence should be gone. There is no point in sticking
comments for things that are non-conditions to begin with. This was
clearly AI-generated, as the kind of things that agents like adding
for nothing based on solely the input provided..

+      The worker is then restarted or unregistered as after any other exit,
+      depending on its exit code and <structfield>bgw_restart_time</structfield>.

Okay, fine.

-      <command>CREATE DATABASE</command>. Specifically, the postmaster sends a
-      termination signal when any of these commands affect the worker's database:
+      <command>CREATE DATABASE</command>. Specifically, a termination signal is
+      sent when any of these commands affect the worker's database:

That's a good addition.
--
Michael

#20Xuneng Zhou
xunengzhou@gmail.com
In reply to: Michael Paquier (#19)
Re: BGWORKER_INTERRUPTIBLE workers are never restarted after DROP/ALTER/CREATE DATABASE

On Tue, Nov 10, 2026 at 1:42 PM Michael Paquier <michael@paquier.xyz> wrote:

On Mon, Nov 09, 2026 at 06:49:41PM +0800, Xuneng Zhou wrote:

There seems to be a risk of interpreting the exit of worker & its PID
reuse as non-exit failure here. How about adding a new critera like
process start time for checking that as V4 does.

I'm finding this addition utterly esoteric, because this would be hit
yes, but the risk is dramatically low compared to making the test more
complex to parse. v3 as sent by Zsolt is just but fine to me.

WFM.

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

#21Iwata, Aya
iwata.aya@fujitsu.com
In reply to: Xuneng Zhou (#20)
#22Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Iwata, Aya (#21)
#23Xuneng Zhou
xunengzhou@gmail.com
In reply to: Zsolt Parragi (#22)
#24Michael Paquier
michael@paquier.xyz
In reply to: Zsolt Parragi (#22)
#25Nathan Bossart
nathandbossart@gmail.com
In reply to: Michael Paquier (#24)
#26Michael Paquier
michael@paquier.xyz
In reply to: Nathan Bossart (#25)