public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aya Iwata (Fujitsu) <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
Date: Fri, 3 Oct 2025 09:27:23 +0000
Message-ID: <OS7PR01MB11964335F36BE41021B62EAE8EAE4A@OS7PR01MB11964.jpnprd01.prod.outlook.com> (raw)
Hi,
Background
==========
If the background workers connect to databases, some database-related commands
like ALTER DATABASE RENAME and ALTER DATABASE SET TABLESPACE cannot be done.
Users must do DROP EXTENSION related with workers, or terminate them by themselves
if they want to drop or alter the database.
Proposal
========
Based on above, I would like to propose to terminate background workers automatically
when such SQLs are executed.
This feature allows the DBMS daemon to send a termination signal to background workers
created by users currently operating on the database when executing commands that make
significant changes to the database.
To receive the termination signal, the background worker must call the
AcceptBackgroundWorkerCancel() function, using the database's OID and a flag
indicating whether to terminate. This means existing background worker processes
will not abruptly terminate.
This termination occurs when executing the DROP DATABASE, ALTER DATABASE RENAME TO,
or ALTER DATABASE SET TABLE SPACE commands, which check the existence of processes.
When a user creates a background worker to perform some data processing or monitoring,
and wants to terminate it along with the database deletion, this feature enables
achieving that goal.
The test set for this feature will be shared later.
How do you feel? Your feedback is very welcome.
Regards,
Aya Iwata
Fujitsu Limited
Attachments:
[application/octet-stream] v0001-0001-Allow-background-workers-to-be-terminated.patch (6.5K, 3-v0001-0001-Allow-background-workers-to-be-terminated.patch)
download | inline diff:
From 81f67744ed2b191e1613f2fd8b2ab5a271639429 Mon Sep 17 00:00:00 2001
From: "iwata.aya" <[email protected]>
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
view thread (67+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected]
Subject: Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
In-Reply-To: <OS7PR01MB11964335F36BE41021B62EAE8EAE4A@OS7PR01MB11964.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox