From: Michael Paquier <michael@paquier.xyz>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Allow bgworkers to be terminated for database-related commands
Date: Tue, 06 Jan 2026 05:24:41 +0000
Message-ID: <E1vczYe-004mpL-18@gemulon.postgresql.org> (raw)
Allow bgworkers to be terminated for database-related commands
Background workers gain a new flag, called BGWORKER_INTERRUPTIBLE, that
offers the possibility to terminate the workers when these are connected
to a database that is involved in one of the following commands:
ALTER DATABASE RENAME TO
ALTER DATABASE SET TABLESPACE
CREATE DATABASE
DROP DATABASE
This is useful to give background workers the same behavior as backends
and autovacuum workers, which are stopped when these commands are
executed. The default behavior, that exists since 9.3, is still to
never terminate bgworkers connected to the database involved in any of
these commands. The new flag has to be set to terminate the workers.
A couple of tests are added to worker_spi to track the commands that
impact the termination of the workers. There is a test case for a
non-interruptible worker, additionally, that relies on an injection
point to make the wait time in CountOtherDBBackends() reduced from 5s to
0.3s for faster test runs. The tests rely on the contents of the server
logs to check if a worker has been started or terminated:
- LOG generated by worker_spi_main() at startup, once connection to
database is done.
- FATAL in bgworker_die() when terminated.
A couple of tests run in the CI have showed that this method is stable
enough. The safe_psql() calls that scan pg_stat_activity could be
replaced with some poll_query_until() for more stability, if the current
method proves to be an issue in the buildfarm.
Author: Aya Iwata <iwata.aya@fujitsu.com>
Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Ryo Matsumura <matsumura.ryo@fujitsu.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Discussion: https://postgr.es/m/OS7PR01MB11964335F36BE41021B62EAE8EAE4A@OS7PR01MB11964.jpnprd01.prod.outlook.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/f1e251be80a05cb66191d5ea48f2cdc3acff72b5
Modified Files
--------------
doc/src/sgml/bgworker.sgml | 23 ++++
src/backend/postmaster/bgworker.c | 51 +++++++
src/backend/storage/ipc/procarray.c | 29 +++-
src/include/postmaster/bgworker.h | 10 ++
src/test/modules/worker_spi/Makefile | 4 +
src/test/modules/worker_spi/meson.build | 4 +
.../modules/worker_spi/t/002_worker_terminate.pl | 151 +++++++++++++++++++++
src/test/modules/worker_spi/worker_spi--1.0.sql | 3 +-
src/test/modules/worker_spi/worker_spi.c | 5 +
9 files changed, 274 insertions(+), 6 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: michael@paquier.xyz, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Allow bgworkers to be terminated for database-related commands
In-Reply-To: <E1vczYe-004mpL-18@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox