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

From 81f67744ed2b191e1613f2fd8b2ab5a271639429 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 v0001] Allow background workers to be terminated at DROP
 DATABASE

---
 doc/src/sgml/bgworker.sgml          | 12 ++++
 src/backend/postmaster/bgworker.c   | 88 +++++++++++++++++++++++++++++
 src/backend/storage/ipc/procarray.c |  6 ++
 src/include/postmaster/bgworker.h   | 13 +++++
 4 files changed, 119 insertions(+)

diff --git a/doc/src/sgml/bgworker.sgml b/doc/src/sgml/bgworker.sgml
index 2c393385a91..cef2ef53c15 100644
--- a/doc/src/sgml/bgworker.sgml
+++ b/doc/src/sgml/bgworker.sgml
@@ -283,6 +283,18 @@ typedef struct BackgroundWorker
    <literal>BGWH_POSTMASTER_DIED</literal>.
   </para>
 
+  <para>
+   By using <function>AcceptBackgroundWorkerCancel(<parameter>oid</parameter>,
+   <parameter>int</parameter>)</function> with the database's OID and a flag
+   value indicating whether to cancel, the DBMS daemon can issue a termination
+   signal to the background worker when changes occur in the database it is
+   connected to, thereby terminating the target 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>
+
   <para>
    Background workers can send asynchronous notification messages, either by
    using the <command>NOTIFY</command> command via <acronym>SPI</acronym>,
diff --git a/src/backend/postmaster/bgworker.c b/src/backend/postmaster/bgworker.c
index 1ad65c237c3..bcdd931af06 100644
--- a/src/backend/postmaster/bgworker.c
+++ b/src/backend/postmaster/bgworker.c
@@ -356,6 +356,22 @@ BackgroundWorkerStateChange(bool allow_new_workers)
 			return;
 		}
 
+		/*
+		 * Set shmem slot number, and initialize cancel flags.
+		 */
+		rw->rw_worker.bgw_shmem_slot = slotno;
+
+		rw->rw_worker.bgw_cancel_databaseId = InvalidOid;
+		rw->rw_worker.bgw_cancel_flags = BGWORKER_CANCEL_NOACCEPT;
+
+		/*
+		 * Update the contents in the shared memory also, these are used in
+		 * EXEC_BACKEND (win32) case
+		 */
+		slot->worker.bgw_shmem_slot = slotno;
+		slot->worker.bgw_cancel_databaseId = InvalidOid;
+		slot->worker.bgw_cancel_flags = BGWORKER_CANCEL_NOACCEPT;
+
 		/*
 		 * Copy strings in a paranoid way.  If shared memory is corrupted, the
 		 * source data might not even be NUL-terminated.
@@ -1396,3 +1412,75 @@ GetBackgroundWorkerTypeByPid(pid_t pid)
 
 	return result;
 }
+
+/*
+ * Accept background worker cancel.
+ * Set cancel flags and databaseId.
+ */
+void
+AcceptBackgroundWorkerCancel(Oid databaseId, int cancel_flags)
+{
+	int			slotno;
+	BackgroundWorkerSlot *slot;
+
+	/* Get shmem slot number from BGW entry. */
+	Assert(MyBgworkerEntry);
+	slotno = MyBgworkerEntry->bgw_shmem_slot;
+
+	/* Get shmem slot address. */
+	Assert(slotno < BackgroundWorkerData->total_slots);
+	slot = &BackgroundWorkerData->slot[slotno];
+
+	/* Set cancel flags and databaseId to sgmem slot. */
+	/* 1st, set databaseId. */
+	slot->worker.bgw_cancel_databaseId = databaseId;
+	/* 2nd, set cancel flags. */
+	slot->worker.bgw_cancel_flags = cancel_flags;
+
+	/*
+	 * This operation doesn't need LOCK, because 'bgw_cancel_flags' is 32bit
+	 * value.
+	 */
+}
+
+/*
+ * 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)
+		{
+			/* 1st, check cancel flags. */
+			if (slot->worker.bgw_cancel_flags & cancel_flags)
+			{
+				/* 2nd, compare databaseId. */
+				if (slot->worker.bgw_cancel_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..8cf49f5810b 100644
--- a/src/include/postmaster/bgworker.h
+++ b/src/include/postmaster/bgworker.h
@@ -66,8 +66,14 @@
  * background workers should not use this class.
  */
 #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				0x0001
 
 typedef void (*bgworker_main_type) (Datum main_arg);
 
@@ -98,6 +104,9 @@ typedef struct BackgroundWorker
 	Datum		bgw_main_arg;
 	char		bgw_extra[BGW_EXTRALEN];
 	pid_t		bgw_notify_pid; /* SIGUSR1 this backend on start/stop */
+	int			bgw_shmem_slot; /* shmem slot ID */
+	Oid			bgw_cancel_databaseId;	/* cancel target */
+	int			bgw_cancel_flags;	/* cancel by admin commands */
 } BackgroundWorker;
 
 typedef enum BgwHandleStatus
@@ -161,4 +170,8 @@ extern void BackgroundWorkerInitializeConnectionByOid(Oid dboid, Oid useroid, ui
 extern void BackgroundWorkerBlockSignals(void);
 extern void BackgroundWorkerUnblockSignals(void);
 
+/* Cancel background workers. */
+extern void AcceptBackgroundWorkerCancel(Oid databaseId, int cancel_flags);
+extern void CancelBackgroundWorkers(Oid databaseId, int cancel_flags);
+
 #endif							/* BGWORKER_H */
-- 
2.39.3