v0002-0001-Allow-background-workers-to-be-terminated.patch

From 172b7df5273d1e90d8d9d550607a17ba6859d197 Mon Sep 17 00:00:00 2001
From: "iwata.aya" <iwata.aya@fujitsu.com>
Date: Thu, 11 Sep 2025 21:16:51 +0900
Subject: [PATCH v0002] Allow background workers to be terminated at DROP
 DATABASE

---
 doc/src/sgml/bgworker.sgml                    | 17 ++++
 src/backend/postmaster/bgworker.c             | 49 ++++++++++++
 src/backend/storage/ipc/procarray.c           |  6 ++
 src/include/postmaster/bgworker.h             | 16 ++++
 src/test/modules/worker_spi/meson.build       |  1 +
 .../worker_spi/t/002_worker_terminate.pl      | 80 +++++++++++++++++++
 src/test/modules/worker_spi/worker_spi.c      |  7 +-
 7 files changed, 174 insertions(+), 2 deletions(-)
 create mode 100644 src/test/modules/worker_spi/t/002_worker_terminate.pl

diff --git a/doc/src/sgml/bgworker.sgml b/doc/src/sgml/bgworker.sgml
index 2c393385a91..dd3d529d762 100644
--- a/doc/src/sgml/bgworker.sgml
+++ b/doc/src/sgml/bgworker.sgml
@@ -108,6 +108,23 @@ typedef struct BackgroundWorker
      </listitem>
     </varlistentry>
 
+    <varlistentry>
+     <term><literal>BGWORKER_EXIT_AT_DATABASE_DROP</literal></term>
+     <listitem>
+      <para>
+       <indexterm><primary>BGWORKER_EXIT_AT_DATABASE_DROP</primary></indexterm>
+       Requests to terminate background worker when the database connected by
+       the background worker is changed. DBMS damon can issue a termination
+       signal to the background worker.
+       This occurs only when significant changes affecting the entire database
+       take place.
+       Specifically, major changes include when the <command>DROP DATABASE</command>,
+       <command>ALTER DATABASE RENAME TO</command>, and
+       <command>ALTER DATABASE SET TABLESPACE</command> commands are executed.
+      </para>
+     </listitem>
+    </varlistentry>
+
    </variablelist>
 
   </para>
diff --git a/src/backend/postmaster/bgworker.c b/src/backend/postmaster/bgworker.c
index 1ad65c237c3..337efca38bd 100644
--- a/src/backend/postmaster/bgworker.c
+++ b/src/backend/postmaster/bgworker.c
@@ -26,6 +26,7 @@
 #include "storage/lwlock.h"
 #include "storage/pmsignal.h"
 #include "storage/proc.h"
+#include "storage/procarray.h"
 #include "storage/procsignal.h"
 #include "storage/shmem.h"
 #include "tcop/tcopprot.h"
@@ -1396,3 +1397,51 @@ GetBackgroundWorkerTypeByPid(pid_t pid)
 
 	return result;
 }
+
+
+/*
+ * Cancel background workers.
+ */
+void
+CancelBackgroundWorkers(Oid databaseId, int cancel_flags)
+{
+	int			slotno;
+	bool		signal_postmaster = false;
+
+	LWLockAcquire(BackgroundWorkerLock, LW_EXCLUSIVE);
+
+	for (slotno = 0; slotno < BackgroundWorkerData->total_slots; ++slotno)
+	{
+		BackgroundWorkerSlot *slot = &BackgroundWorkerData->slot[slotno];
+
+		/* Check worker slot. */
+		if (!slot->in_use)
+			continue;
+
+		/* 1st, check cancel flags. */
+		if ((slot->worker.bgw_flags & BGWORKER_EXIT_AT_DATABASE_DROP) & cancel_flags)
+		{
+			PGPROC     *proc = BackendPidGetProc(slot->pid);
+
+			if (!proc)
+				continue;
+
+			/* 2nd, compare databaseId. */
+			if (proc->databaseId == databaseId)
+			{
+				/*
+				 * Set terminate flag in shared memory, unless slot has
+				 * been reused.
+				 */
+				slot->terminate = true;
+				signal_postmaster = true;
+			}
+		}
+	}
+
+	LWLockRelease(BackgroundWorkerLock);
+
+	/* Make sure the postmaster notices the change to shared memory. */
+	if (signal_postmaster)
+		SendPostmasterSignal(PMSIGNAL_BACKGROUND_WORKER_CHANGE);
+}
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 200f72c6e25..36571354324 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -56,6 +56,7 @@
 #include "catalog/pg_authid.h"
 #include "miscadmin.h"
 #include "pgstat.h"
+#include "postmaster/bgworker.h"
 #include "port/pg_lfind.h"
 #include "storage/proc.h"
 #include "storage/procarray.h"
@@ -3768,6 +3769,11 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
 		for (index = 0; index < nautovacs; index++)
 			(void) kill(autovac_pids[index], SIGTERM);	/* ignore any error */
 
+		/*
+		 * Cancel background workers by admin commands.
+		 */
+		CancelBackgroundWorkers(databaseId, BGWORKER_CANCEL_ADMIN_COMMANDS);
+
 		/* sleep, then try again */
 		pg_usleep(100 * 1000L); /* 100ms */
 	}
diff --git a/src/include/postmaster/bgworker.h b/src/include/postmaster/bgworker.h
index 058667a47a0..637cd753c22 100644
--- a/src/include/postmaster/bgworker.h
+++ b/src/include/postmaster/bgworker.h
@@ -59,6 +59,14 @@
  */
 #define BGWORKER_BACKEND_DATABASE_CONNECTION		0x0002
 
+/*
+ * This flag means the bgworker must be exit when the connecting database is
+ * being dropped or moved.
+ * It requires both BGWORKER_SHMEM_ACCESS and
+ * BGWORKER_BACKEND_DATABASE_CONNECTION were passed too.
+ */
+#define BGWORKER_EXIT_AT_DATABASE_DROP                         0x0004
+
 /*
  * This class is used internally for parallel queries, to keep track of the
  * number of active parallel workers and make sure we never launch more than
@@ -68,6 +76,11 @@
 #define BGWORKER_CLASS_PARALLEL					0x0010
 /* add additional bgworker classes here */
 
+/*
+ * Flags for cancel by admin commands.
+ */
+#define BGWORKER_CANCEL_NOACCEPT					0x0000
+#define BGWORKER_CANCEL_ADMIN_COMMANDS				0x0004
 
 typedef void (*bgworker_main_type) (Datum main_arg);
 
@@ -161,4 +174,7 @@ extern void BackgroundWorkerInitializeConnectionByOid(Oid dboid, Oid useroid, ui
 extern void BackgroundWorkerBlockSignals(void);
 extern void BackgroundWorkerUnblockSignals(void);
 
+/* Cancel background workers. */
+extern void CancelBackgroundWorkers(Oid databaseId, int cancel_flags);
+
 #endif							/* BGWORKER_H */
diff --git a/src/test/modules/worker_spi/meson.build b/src/test/modules/worker_spi/meson.build
index d673ece48a0..1d30048aec8 100644
--- a/src/test/modules/worker_spi/meson.build
+++ b/src/test/modules/worker_spi/meson.build
@@ -28,6 +28,7 @@ tests += {
   'tap': {
     'tests': [
       't/001_worker_spi.pl',
+      't/002_worker_terminate.pl'
     ],
   },
 }
diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/test/modules/worker_spi/t/002_worker_terminate.pl
new file mode 100644
index 00000000000..78dcd40ae34
--- /dev/null
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -0,0 +1,80 @@
+# Copyright (c) 2023-2025, PostgreSQL Global Development Group
+
+# Test background workers can be terminated when ALTER/DROP DATABASE happens
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Ensure the worker_spi dynamic worker is launched on the specified database
+sub launch_bgworker
+{
+	my ($node, $database) = @_;
+
+	# Launch a background worker on the given database
+	my $result = $node->safe_psql(
+		$database, qq(
+        SELECT worker_spi_launch(4, oid) IS NOT NULL
+        FROM pg_database WHERE datname = '$database';
+    ));
+	is($result, 't', "dynamic bgworker launched");
+
+	# Check the worker is running correctly
+	$result = $node->safe_psql('postgres',
+		"SELECT count(1) FROM pg_stat_activity WHERE datname = '$database' AND backend_type = 'worker_spi dynamic';"
+	);
+	is($result, 1, 'dynamic bgworker has exist');
+}
+
+# Run the given query and verify the background worker can be terminated
+sub run_db_command
+{
+	my ($node, $command, $testname) = @_;
+	my $offset = -s $node->logfile;
+
+	$node->safe_psql('postgres', $command);
+	ok( $node->log_contains(
+			"terminating background worker \"worker_spi dynamic\" due to administrator command",
+			$offset),
+		"background worker can be terminated at $testname");
+}
+
+my $node = PostgreSQL::Test::Cluster->new('mynode');
+$node->init;
+$node->start;
+
+# Create another database. "postgres" is still used to run db commands and
+# checks the pg_stat_activity.
+$node->safe_psql('postgres', 'CREATE DATABASE testdb');
+$node->safe_psql('testdb', 'CREATE EXTENSION worker_spi;');
+
+launch_bgworker($node, 'testdb');
+
+# Testcase 1: ALTER DATABASE SET TABLESPACE
+run_db_command(
+	$node,
+	"ALTER DATABASE testdb RENAME TO renameddb",
+	"ALTER DATABASE SET TABLESPACE");
+
+# Preparation for the next test; create another tablespace
+my $basedir = $node->basedir();
+my $tablespace = "$basedir/tablespace";
+mkdir($tablespace);
+$node->safe_psql('postgres',
+	"CREATE TABLESPACE test_tablespace LOCATION '$tablespace'");
+launch_bgworker($node, 'renameddb');
+
+# Testcase 2: ALTER DATABASE SET TABLESPACE
+run_db_command(
+	$node,
+	"ALTER DATABASE renameddb SET TABLESPACE test_tablespace",
+	"ALTER DATABASE SET TABLESPACE");
+
+launch_bgworker($node, 'renameddb');
+
+# Testcase 2: DROP DATABASE
+run_db_command($node, "DROP DATABASE renameddb", "DROP DATABASE");
+
+done_testing();
diff --git a/src/test/modules/worker_spi/worker_spi.c b/src/test/modules/worker_spi/worker_spi.c
index bea8339f464..02bed2ac110 100644
--- a/src/test/modules/worker_spi/worker_spi.c
+++ b/src/test/modules/worker_spi/worker_spi.c
@@ -128,6 +128,7 @@ initialize_worker_spi(worktable *table)
 	CommitTransactionCommand();
 	debug_query_string = NULL;
 	pgstat_report_activity(STATE_IDLE, NULL);
+
 }
 
 void
@@ -361,7 +362,8 @@ _PG_init(void)
 	/* set up common data for all our workers */
 	memset(&worker, 0, sizeof(worker));
 	worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
-		BGWORKER_BACKEND_DATABASE_CONNECTION;
+		BGWORKER_BACKEND_DATABASE_CONNECTION |
+		BGWORKER_EXIT_AT_DATABASE_DROP;
 	worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
 	worker.bgw_restart_time = BGW_NEVER_RESTART;
 	sprintf(worker.bgw_library_name, "worker_spi");
@@ -407,7 +409,8 @@ worker_spi_launch(PG_FUNCTION_ARGS)
 
 	memset(&worker, 0, sizeof(worker));
 	worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
-		BGWORKER_BACKEND_DATABASE_CONNECTION;
+		BGWORKER_BACKEND_DATABASE_CONNECTION |
+		BGWORKER_EXIT_AT_DATABASE_DROP;
 	worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
 	worker.bgw_restart_time = BGW_NEVER_RESTART;
 	sprintf(worker.bgw_library_name, "worker_spi");
-- 
2.39.3