public inbox for [email protected]
help / color / mirror / Atom feedRE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
15+ messages / 5 participants
[nested] [flat]
* RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
@ 2026-01-05 15:06 Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Aya Iwata (Fujitsu) @ 2026-01-05 15:06 UTC (permalink / raw)
To: 'Peter Smith' <[email protected]>; Michael Paquier <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
Hi
Thank you for your comments and suggestions on the previous version patches!
I believe this feature become much better.
I've created a v13 patch incorporating Peter-san's suggestions.
> One issue with the test as written, as of run_db_command(), is that we
> make sure that a worker is stopped by scanning the output of the logs.
> This approach may detect incorrect patterns, unfortunately. For
> example, if the termination logic has a bug it may be possible that
> the worker found as terminated is the first one created by the test,
> which we expect to always run. While the log is mandatory to have, I
> have a suggestion to make that even better: let's keep track in
> run_db_command() of the PIDs of the worker processes we expect to
> exist after running each database command, then make sure that the
> list of PIDs match with what we expect. This is a bit simpler in the
> case of this test as we only expect one matching PID.
I've also reviewed the test set code comparing PIDs. I think this is acceptable.
Best Regards,
Aya Iwata
Attachments:
[application/octet-stream] v0013-0001-Allow-background-workers-to-be-terminated-at-D.patch (15.1K, 2-v0013-0001-Allow-background-workers-to-be-terminated-at-D.patch)
download | inline diff:
From 931400da984a24c45af4078466149ddd82e873d7 Mon Sep 17 00:00:00 2001
From: "iwata.aya" <[email protected]>
Date: Thu, 11 Sep 2025 21:16:51 +0900
Subject: [PATCH v0013] Allow background workers to be terminated at DROP
DATABASE
---
doc/src/sgml/bgworker.sgml | 23 +++
src/backend/postmaster/bgworker.c | 51 ++++++
src/backend/storage/ipc/procarray.c | 28 +++-
src/include/postmaster/bgworker.h | 8 +
src/test/modules/worker_spi/Makefile | 4 +
src/test/modules/worker_spi/meson.build | 4 +
.../worker_spi/t/002_worker_terminate.pl | 148 ++++++++++++++++++
.../modules/worker_spi/worker_spi--1.0.sql | 3 +-
src/test/modules/worker_spi/worker_spi.c | 5 +
9 files changed, 268 insertions(+), 6 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..4699ef6345f 100644
--- a/doc/src/sgml/bgworker.sgml
+++ b/doc/src/sgml/bgworker.sgml
@@ -108,6 +108,29 @@ typedef struct BackgroundWorker
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>BGWORKER_INTERRUPTIBLE</literal></term>
+ <listitem>
+ <para>
+ <indexterm><primary>BGWORKER_INTERRUPTIBLE</primary></indexterm>
+ Requests termination of the background worker when its connected database is
+ dropped, renamed, moved to a different tablespace, or used as a template for
+ <command>CREATE DATABASE</command>. Specifically, the postmaster sends a
+ termination signal when any of these commands affect the worker's database:
+ <itemizedlist>
+ <listitem><para><command>DROP DATABASE</command></para></listitem>
+ <listitem><para><command>ALTER DATABASE RENAME
+ TO</command></para></listitem>
+ <listitem><para><command>ALTER DATABASE SET
+ TABLESPACE</command></para></listitem>
+ <listitem><para><command>CREATE DATABASE</command></para></listitem>
+ </itemizedlist>
+ Requires both <literal>BGWORKER_SHMEM_ACCESS</literal> and
+ <literal>BGWORKER_BACKEND_DATABASE_CONNECTION</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff --git a/src/backend/postmaster/bgworker.c b/src/backend/postmaster/bgworker.c
index af02eea754f..61bd5d60791 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"
@@ -665,6 +666,17 @@ SanityCheckBackgroundWorker(BackgroundWorker *worker, int elevel)
/* XXX other checks? */
}
+ /* Interruptible workers require a database connection */
+ if ((worker->bgw_flags & BGWORKER_INTERRUPTIBLE) &&
+ !(worker->bgw_flags & BGWORKER_BACKEND_DATABASE_CONNECTION))
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("background worker \"%s\": cannot make background workers interruptible without database access",
+ worker->bgw_name)));
+ return false;
+ }
+
if ((worker->bgw_restart_time < 0 &&
worker->bgw_restart_time != BGW_NEVER_RESTART) ||
(worker->bgw_restart_time > USECS_PER_DAY / 1000))
@@ -1399,3 +1411,42 @@ GetBackgroundWorkerTypeByPid(pid_t pid)
return result;
}
+
+/*
+ * Terminate all background workers connected to the given database, if they
+ * have requested it.
+ */
+void
+TerminateInterruptibleBgWorkersByDbOid(Oid databaseId)
+{
+ bool signal_postmaster = false;
+
+ LWLockAcquire(BackgroundWorkerLock, LW_EXCLUSIVE);
+
+ /*
+ * Iterate through slots, looking for workers connected to the given
+ * database.
+ */
+ for (int slotno = 0; slotno < BackgroundWorkerData->total_slots; slotno++)
+ {
+ BackgroundWorkerSlot *slot = &BackgroundWorkerData->slot[slotno];
+
+ if (slot->in_use &&
+ (slot->worker.bgw_flags & BGWORKER_INTERRUPTIBLE))
+ {
+ PGPROC *proc = BackendPidGetProc(slot->pid);
+
+ if (proc && proc->databaseId == databaseId)
+ {
+ 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 52f886bf84f..347d50db1bf 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -56,11 +56,13 @@
#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"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/injection_point.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
@@ -3687,8 +3689,9 @@ CountUserBackends(Oid roleid)
* CountOtherDBBackends -- check for other backends running in the given DB
*
* If there are other backends in the DB, we will wait a maximum of 5 seconds
- * for them to exit. Autovacuum backends are encouraged to exit early by
- * sending them SIGTERM, but normal user backends are just waited for.
+ * for them to exit. Autovacuum backends and background workers are encouraged
+ * to exit early by sending them PMSIGNAL_BACKGROUND_WORKER_CHANGE, but normal
+ * user backends are just waited for.
*
* The current backend is always ignored; it is caller's responsibility to
* check whether the current backend uses the given DB, if it's important.
@@ -3713,10 +3716,19 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
#define MAXAUTOVACPIDS 10 /* max autovacs to SIGTERM per iteration */
int autovac_pids[MAXAUTOVACPIDS];
- int tries;
- /* 50 tries with 100ms sleep between tries makes 5 sec total wait */
- for (tries = 0; tries < 50; tries++)
+ /*
+ * Retry up to 50 times with 100ms between attempts (max 5s total). Can be
+ * reduced to 3 attempts (max 0.3s total) to speed up tests.
+ */
+ int ntries = 50;
+
+#ifdef USE_INJECTION_POINTS
+ if (IS_INJECTION_POINT_ATTACHED("reduce-ncounts"))
+ ntries = 3;
+#endif
+
+ for (int tries = 0; tries < ntries; tries++)
{
int nautovacs = 0;
bool found = false;
@@ -3766,6 +3778,12 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
for (index = 0; index < nautovacs; index++)
(void) kill(autovac_pids[index], SIGTERM); /* ignore any error */
+ /*
+ * Terminate all background workers for this database, if they have
+ * requested it (BGWORKER_INTERRUPTIBLE).
+ */
+ TerminateInterruptibleBgWorkersByDbOid(databaseId);
+
/* sleep, then try again */
pg_usleep(100 * 1000L); /* 100ms */
}
diff --git a/src/include/postmaster/bgworker.h b/src/include/postmaster/bgworker.h
index 5f84f48aa0d..8acca22947d 100644
--- a/src/include/postmaster/bgworker.h
+++ b/src/include/postmaster/bgworker.h
@@ -59,6 +59,13 @@
*/
#define BGWORKER_BACKEND_DATABASE_CONNECTION 0x0002
+/*
+ * Exit the bgworker if its database is involved in a CREATE, ALTER or DROP
+ * database command. It requires BGWORKER_SHMEM_ACCESS and
+ * BGWORKER_BACKEND_DATABASE_CONNECTION.
+ */
+#define BGWORKER_INTERRUPTIBLE 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
@@ -128,6 +135,7 @@ extern const char *GetBackgroundWorkerTypeByPid(pid_t pid);
/* Terminate a bgworker */
extern void TerminateBackgroundWorker(BackgroundWorkerHandle *handle);
+extern void TerminateInterruptibleBgWorkersByDbOid(Oid databaseId);
/* This is valid in a running worker */
extern PGDLLIMPORT BackgroundWorker *MyBgworkerEntry;
diff --git a/src/test/modules/worker_spi/Makefile b/src/test/modules/worker_spi/Makefile
index 024b34cdbb3..e7c5c059e32 100644
--- a/src/test/modules/worker_spi/Makefile
+++ b/src/test/modules/worker_spi/Makefile
@@ -6,6 +6,10 @@ EXTENSION = worker_spi
DATA = worker_spi--1.0.sql
PGFILEDESC = "worker_spi - background worker example"
+EXTRA_INSTALL = src/test/modules/injection_points
+
+export enable_injection_points
+
TAP_TESTS = 1
ifdef USE_PGXS
diff --git a/src/test/modules/worker_spi/meson.build b/src/test/modules/worker_spi/meson.build
index e9236bc27e7..6475e23f601 100644
--- a/src/test/modules/worker_spi/meson.build
+++ b/src/test/modules/worker_spi/meson.build
@@ -26,8 +26,12 @@ tests += {
'sd': meson.current_source_dir(),
'bd': meson.current_build_dir(),
'tap': {
+ 'env': {
+ 'enable_injection_points': get_option('injection_points') ? 'yes' : 'no',
+ },
'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..12e6b4517af
--- /dev/null
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -0,0 +1,148 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test background workers can be terminated by db commands
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# This test depends on injection points to detect whether background workers
+# remain.
+if ($ENV{enable_injection_points} ne 'yes')
+{
+ plan skip_all => 'Injection points not supported by this build';
+}
+
+# Ensure the worker_spi dynamic worker is launched on the specified database.
+# Returns the PID of the worker launched.
+sub launch_bgworker
+{
+ my ($node, $database, $testcase, $interruptible) = @_;
+ my $offset = -s $node->logfile;
+
+ # Launch a background worker on the given database.
+ my $pid = $node->safe_psql(
+ $database, qq(
+ SELECT worker_spi_launch($testcase, '$database'::regdatabase, 0, '{}', $interruptible);
+ ));
+
+ # Check that the bgworker is initialized.
+ $node->wait_for_log(
+ qr/LOG: worker_spi dynamic worker $testcase initialized with .*\..*/,
+ $offset);
+ my $result = $node->safe_psql($database,
+ "SELECT count(*) > 0 FROM pg_stat_activity WHERE pid = $pid;");
+ is($result, 't', "dynamic bgworker $testcase launched");
+
+ return $pid;
+}
+
+# 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;
+
+ $node->safe_psql('postgres', $command);
+
+ $node->wait_for_log(
+ qr/terminating background worker \"worker_spi dynamic\" due to administrator command/,
+ $offset);
+
+ my $result = $node->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_stat_activity WHERE pid = $pid;");
+ is($result, 't', "dynamic bgworker stopped for $testname");
+}
+
+my $node = PostgreSQL::Test::Cluster->new('mynode');
+$node->init;
+$node->start;
+
+# Check if the extension injection_points is available, as it may be
+# possible that this script is run with installcheck, where the module
+# would not be installed by default.
+if (!$node->check_extension('injection_points'))
+{
+ plan skip_all => 'Extension injection_points not installed';
+}
+
+$node->safe_psql('postgres', 'CREATE EXTENSION worker_spi;');
+
+# Launch a background worker without BGWORKER_INTERRUPTIBLE.
+my $pid = launch_bgworker($node, 'postgres', 0, 'false');
+
+# Ensure CREATE DATABASE WITH TEMPLATE fails because a non-interruptible
+# bgworker exists.
+
+# The injection point 'reduce-ncounts' reduces the number of backend
+# retries, allowing for shorter test runs. See CountOtherDBBackends().
+$node->safe_psql('postgres', "CREATE EXTENSION injection_points;");
+$node->safe_psql('postgres',
+ "SELECT injection_points_attach('reduce-ncounts', 'error');");
+
+my $stderr;
+
+$node->psql(
+ 'postgres',
+ "CREATE DATABASE testdb WITH TEMPLATE postgres",
+ stderr => \$stderr);
+ok( $stderr =~
+ "source database \"postgres\" is being accessed by other users",
+ "background worker blocked the database creation");
+
+# Confirm that the non-interruptible bgworker is still running.
+my $result = $node->safe_psql(
+ "postgres", qq(
+ SELECT count(1) FROM pg_stat_activity
+ WHERE backend_type = 'worker_spi dynamic';));
+
+is($result, '1',
+ "background worker is still running after CREATE DATABASE WITH TEMPLATE");
+
+# Terminate the non-interruptible worker for the next tests.
+$node->safe_psql(
+ "postgres", qq(
+ SELECT pg_terminate_backend(pid)
+ FROM pg_stat_activity WHERE backend_type = 'worker_spi dynamic';));
+
+# The injection point is not used anymore, release it.
+$node->safe_psql('postgres',
+ "SELECT injection_points_detach('reduce-ncounts');");
+
+# Check that BGWORKER_INTERRUPTIBLE allows background workers to be
+# terminated with database-related commands.
+
+# Test case 1: CREATE DATABASE WITH TEMPLATE
+$pid = launch_bgworker($node, 'postgres', 1, 'true');
+run_bgworker_interruptible_test(
+ $node,
+ "CREATE DATABASE testdb WITH TEMPLATE postgres",
+ "CREATE DATABASE WITH TEMPLATE", $pid);
+
+# Test case 2: ALTER DATABASE RENAME
+$pid = launch_bgworker($node, 'testdb', 2, 'true');
+run_bgworker_interruptible_test(
+ $node,
+ "ALTER DATABASE testdb RENAME TO renameddb",
+ "ALTER DATABASE RENAME", $pid);
+
+# Preparation for the next test, create a tablespace.
+my $tablespace = PostgreSQL::Test::Utils::tempdir;
+$node->safe_psql('postgres',
+ "CREATE TABLESPACE test_tablespace LOCATION '$tablespace'");
+
+# Test case 3: ALTER DATABASE SET TABLESPACE
+$pid = launch_bgworker($node, 'renameddb', 3, 'true');
+run_bgworker_interruptible_test(
+ $node,
+ "ALTER DATABASE renameddb SET TABLESPACE test_tablespace",
+ "ALTER DATABASE SET TABLESPACE", $pid);
+
+# Test case 4: DROP DATABASE
+$pid = launch_bgworker($node, 'renameddb', 4, 'true');
+run_bgworker_interruptible_test($node, "DROP DATABASE renameddb", "DROP DATABASE", $pid);
+
+done_testing();
diff --git a/src/test/modules/worker_spi/worker_spi--1.0.sql b/src/test/modules/worker_spi/worker_spi--1.0.sql
index 84deb6199f6..f5e9621b0d1 100644
--- a/src/test/modules/worker_spi/worker_spi--1.0.sql
+++ b/src/test/modules/worker_spi/worker_spi--1.0.sql
@@ -7,7 +7,8 @@
CREATE FUNCTION worker_spi_launch(index int4,
dboid oid DEFAULT 0,
roleoid oid DEFAULT 0,
- flags text[] DEFAULT '{}')
+ flags text[] DEFAULT '{}',
+ interruptible boolean DEFAULT false)
RETURNS pg_catalog.int4 STRICT
AS 'MODULE_PATHNAME'
LANGUAGE C;
diff --git a/src/test/modules/worker_spi/worker_spi.c b/src/test/modules/worker_spi/worker_spi.c
index 7b0cd2e99b4..d1e4a2bd952 100644
--- a/src/test/modules/worker_spi/worker_spi.c
+++ b/src/test/modules/worker_spi/worker_spi.c
@@ -404,10 +404,15 @@ worker_spi_launch(PG_FUNCTION_ARGS)
Size ndim;
int nelems;
Datum *datum_flags;
+ bool interruptible = PG_GETARG_BOOL(4);
memset(&worker, 0, sizeof(worker));
worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
BGWORKER_BACKEND_DATABASE_CONNECTION;
+
+ if (interruptible)
+ worker.bgw_flags |= BGWORKER_INTERRUPTIBLE;
+
worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
worker.bgw_restart_time = BGW_NEVER_RESTART;
sprintf(worker.bgw_library_name, "worker_spi");
--
2.39.3
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
@ 2026-01-05 21:18 ` Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Peter Smith @ 2026-01-05 21:18 UTC (permalink / raw)
To: Aya Iwata (Fujitsu) <[email protected]>; +Cc: Michael Paquier <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2026 at 2:06 AM Aya Iwata (Fujitsu)
<[email protected]> wrote:
>
> Hi
>
> Thank you for your comments and suggestions on the previous version patches!
> I believe this feature become much better.
>
> I've created a v13 patch incorporating Peter-san's suggestions.
>
Some review comments for v13-0001.
======
src/backend/storage/ipc/procarray.c
CountOtherDBBackends:
1.
* If there are other backends in the DB, we will wait a maximum of 5 seconds
- * for them to exit. Autovacuum backends are encouraged to exit early by
- * sending them SIGTERM, but normal user backends are just waited for.
+ * for them to exit. Autovacuum backends and background workers are encouraged
+ * to exit early by sending them PMSIGNAL_BACKGROUND_WORKER_CHANGE, but normal
+ * user backends are just waited for.
This did not seem like the correct fix for my previous review comment
[1, comment #3], because the autovacuum backends are still killed with
SIGTERM, right?
======
.../worker_spi/t/002_worker_terminate.pl
2.
+# Confirm that the non-interruptible bgworker is still running.
+my $result = $node->safe_psql(
+ "postgres", qq(
+ SELECT count(1) FROM pg_stat_activity
+ WHERE backend_type = 'worker_spi dynamic';));
The indentation of the "SELECT" still does not look correct to me. Did
you run pgperltidy on this file?
======
[1] https://www.postgresql.org/message-id/CAHut%2BPtSVYKU4vfaRev4FMdbeZ3ukvxRy4X7uK05jv_9WMYafA%40mail.g...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
@ 2026-01-06 05:38 ` Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-01-06 05:38 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: Aya Iwata (Fujitsu) <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
On Tue, Jan 06, 2026 at 08:18:39AM +1100, Peter Smith wrote:
> This did not seem like the correct fix for my previous review comment
> [1, comment #3], because the autovacuum backends are still killed with
> SIGTERM, right?
Yes, you are right that this creates a confusing mix. Bgworkers have
no relationship with SIGTERM in this code. I have added a sentence to
document the new behavior for interruptible bgworkers, after the
existing ones. One thing that was also missing is the fact that the
wait can be shortened with the injection point.
> 2.
> +# Confirm that the non-interruptible bgworker is still running.
> +my $result = $node->safe_psql(
> + "postgres", qq(
> + SELECT count(1) FROM pg_stat_activity
> + WHERE backend_type = 'worker_spi dynamic';));
>
> The indentation of the "SELECT" still does not look correct to me. Did
> you run pgperltidy on this file?
perltidy is happy with that, because it considers the string within
the quoted qq() area as something fine. At the end this is just a tab
vs whitespace issue, you are right that this should use whitespaces.
I have done a couple of test runs in the CI to be sure, and noticed no
instability in the tests, so applied. I'll keep an eye on the CFbot
and the buildfarm for the next few days.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-13 11:38 ` Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Aya Iwata (Fujitsu) @ 2026-03-13 11:38 UTC (permalink / raw)
To: 'Michael Paquier' <[email protected]>; Peter Smith <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
Hi
On March 10th, the bgworker test "t/002_worker_terminate.pl" failed on the Build Farm.
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jay&dt=2026-03-10%2019%3A26%3A19
We are currently attempting to reproduce this issue and are considering a fix.
According to the log, the test failed because the bgworker cannot be terminated
within the time frame (5 seconds) when ALTER DATABASE RENAME is executed.
So I suspect that there is a lock conflict.
Log of Build Farm;
2026-03-10 21:08:33.103 CET [28076:4] 002_worker_terminate.pl LOG: statement: ALTER DATABASE testdb RENAME TO renameddb
2026-03-10 21:08:34.578 CET [28071:2] FATAL: terminating background worker "worker_spi dynamic" due to administrator command
2026-03-10 21:08:38.109 CET [28076:5] 002_worker_terminate.pl ERROR: database "testdb" is being accessed by other users
2026-03-10 21:08:38.109 CET [28076:6] 002_worker_terminate.pl DETAIL: There is 1 other session using the database.
2026-03-10 21:08:38.109 CET [28076:7] 002_worker_terminate.pl STATEMENT: ALTER DATABASE testdb RENAME TO renameddb
2026-03-10 21:08:38.229 CET [28076:8] 002_worker_terminate.pl LOG: disconnection: session time: 0:00:05.129 user=buildfarm database=postgres host=[local]
2026-03-10 21:08:38.231 CET [28025:6] LOG: background worker "worker_spi dynamic" (PID 28071) exited with exit code 1
2026-03-10 21:08:38.234 CET [28025:7] LOG: received immediate shutdown request
2026-03-10 21:08:38.241 CET [28025:8] LOG: database system is shut down
If there is a lock conflict, we will come up with a solution.
Regards,
Aya Iwata
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
@ 2026-03-16 07:23 ` Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-16 07:23 UTC (permalink / raw)
To: Aya Iwata (Fujitsu) <[email protected]>; +Cc: Peter Smith <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
On Fri, Mar 13, 2026 at 11:38:51AM +0000, Aya Iwata (Fujitsu) wrote:
> On March 10th, the bgworker test "t/002_worker_terminate.pl" failed on the Build Farm.
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jay&dt=2026-03-10%2019%3A26%3A19
Indeed.
> We are currently attempting to reproduce this issue and are considering a fix.
>
> According to the log, the test failed because the bgworker cannot be terminated
> within the time frame (5 seconds) when ALTER DATABASE RENAME is executed.
>
> So I suspect that there is a lock conflict.
Aye, we have a timing issue here, and at first it seemed to me that
this was a bug in the backend logic. But actually I suspect that it
is simpler than that: we don't disable autovacuum so couldn't an
autovacuum worker connect to the database "testdb" that we are trying
to rename in this query? I cannot be 100% sure because we have
reduced the log activity for the sake of the tests, but that feels
possible to me. We could try to disable autovacuum entirely, then see
if the situation gets better in the buildfarm.
The failure rate is so low that it is likely going to take a few weeks
to check the stability of the situation. We could also lot more
things, of course, as a temporary solution.
What do you think?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-16 23:37 ` Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-16 23:37 UTC (permalink / raw)
To: Aya Iwata (Fujitsu) <[email protected]>; +Cc: Peter Smith <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; pgsql-hackers
On Mon, Mar 16, 2026 at 04:23:40PM +0900, Michael Paquier wrote:
> Aye, we have a timing issue here, and at first it seemed to me that
> this was a bug in the backend logic. But actually I suspect that it
> is simpler than that: we don't disable autovacuum so couldn't an
> autovacuum worker connect to the database "testdb" that we are trying
> to rename in this query? I cannot be 100% sure because we have
> reduced the log activity for the sake of the tests, but that feels
> possible to me. We could try to disable autovacuum entirely, then see
> if the situation gets better in the buildfarm.
>
> The failure rate is so low that it is likely going to take a few weeks
> to check the stability of the situation. We could also lot more
> things, of course, as a temporary solution.
After sleeping on it, I do not have a better idea than the attached,
so we could always get that into the tree and see if the situation
improves, even if it would take time. Iwata-san and others, what do
you think? Perhaps you could think of a different reason causing this
failure?
--
Michael
diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/test/modules/worker_spi/t/002_worker_terminate.pl
index 6d3794355981..21dfb5961289 100644
--- a/src/test/modules/worker_spi/t/002_worker_terminate.pl
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -59,6 +59,10 @@ sub run_bgworker_interruptible_test
my $node = PostgreSQL::Test::Cluster->new('mynode');
$node->init;
+$node->append_conf(
+ "postgresql.conf", qq(
+autovacuum = off
+));
$node->start;
# Check if the extension injection_points is available, as it may be
Attachments:
[text/plain] worker-spi-test.patch (564B, 2-worker-spi-test.patch)
download | inline diff:
diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/test/modules/worker_spi/t/002_worker_terminate.pl
index 6d3794355981..21dfb5961289 100644
--- a/src/test/modules/worker_spi/t/002_worker_terminate.pl
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -59,6 +59,10 @@ sub run_bgworker_interruptible_test
my $node = PostgreSQL::Test::Cluster->new('mynode');
$node->init;
+$node->append_conf(
+ "postgresql.conf", qq(
+autovacuum = off
+));
$node->start;
# Check if the extension injection_points is available, as it may be
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-17 01:55 ` =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= @ 2026-03-17 01:55 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; =?iso-2022-jp?B?SXdhdGEsIEF5YS8bJEI0ZEVEGyhCIBskQjpMGyhC?= <[email protected]>; +Cc: Peter Smith <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
Dear Michael,
> After sleeping on it, I do not have a better idea than the attached,
> so we could always get that into the tree and see if the situation
> improves, even if it would take time. Iwata-san and others, what do
> you think? Perhaps you could think of a different reason causing this
> failure?
I also had a discussion with her off-list, but I could not find neither.
Additional idea is to increase the log level to make analysis easier, but not
sure what value is appropriate. Is it enough to raise to DEBUG1?
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
@ 2026-03-17 03:57 ` Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-17 03:57 UTC (permalink / raw)
To: Kuroda, Hayato/黒田 隼人 <[email protected]>; +Cc: Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
On Tue, Mar 17, 2026 at 01:55:05AM +0000, Kuroda, Hayato/黒田 隼人 wrote:
> I also had a discussion with her off-list, but I could not find neither.
> Additional idea is to increase the log level to make analysis easier, but not
> sure what value is appropriate. Is it enough to raise to DEBUG1?
That would be an option. Let's do that if we see another failure
after disabling autovacuum. Another thing I have noticed is
debug_parallel_query=regress for this animal, which may encourage
parallel workers, so I have also disabled it. Let's see first if we
get more failures after af8837a10bc7.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-17 06:00 ` Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Lakhin @ 2026-03-17 06:00 UTC (permalink / raw)
To: Iwata, Aya/岩田 彩 <[email protected]>; Michael Paquier <[email protected]>; +Cc: Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
Hello Michael and Iwata-san,
17.03.2026 05:57, Michael Paquier wrote:
> On Tue, Mar 17, 2026 at 01:55:05AM +0000, Kuroda, Hayato/黒田 隼人 wrote:
>> I also had a discussion with her off-list, but I could not find neither.
>> Additional idea is to increase the log level to make analysis easier, but not
>> sure what value is appropriate. Is it enough to raise to DEBUG1?
> That would be an option. Let's do that if we see another failure
> after disabling autovacuum. Another thing I have noticed is
> debug_parallel_query=regress for this animal, which may encourage
> parallel workers, so I have also disabled it. Let's see first if we
> get more failures after af8837a10bc7.
> --
I've noticed another failure of the test from widowbird: [1]. But probably
it's not related to f1e251be8 directly, as widowbird failed with a similar
error before: [2]. I couldn't reproduce such failures yet, but keep
trying in the background...
[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2026-03-16%2009%3A35%3A03
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2025-10-25%2010%3A35%3A03
Best regards,
Alexander
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
@ 2026-03-17 08:33 ` Michael Paquier <[email protected]>
2026-03-19 00:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-17 08:33 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
On Tue, Mar 17, 2026 at 08:00:00AM +0200, Alexander Lakhin wrote:
> I've noticed another failure of the test from widowbird: [1]. But probably
> it's not related to f1e251be8 directly, as widowbird failed with a similar
> error before: [2]. I couldn't reproduce such failures yet, but keep
> trying in the background...
>
> [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2026-03-16%2009%3A35%3A03
> [2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2025-10-25%2010%3A35%3A03
Thanks. widowbird also uses "debug_parallel_query = regress", so it
really looks like to me that this is related to a parallel worker
spawning and messing with the database being renamed.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-19 00:54 ` Michael Paquier <[email protected]>
2026-03-21 02:46 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-19 00:54 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Alexander Lakhin <[email protected]>; Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
On Wed, Mar 18, 2026 at 03:52:02PM -0400, Tom Lane wrote:
> which makes me wonder whether the problematic session is the second or
> third bgworker. I am not seeing entries indicating that those
> stopped, as there is for the first bgworker.
Looking at the logs produced at [1], the worker launched as number 1
would not be able to interact, it connects to the database postgres,
under PID 1616001, and is reported as exited by the postmaster.
The only interacting sessions would be:
1) The bgworker launched as number 2, connected to database testdb.
2) The session checking for pg_stat_activity, launched by
launch_bgworker(). The test was connected with the database we want
to rename, and this could interact as an extra session. This query
could be run while connected to the database postgres to reduce the
friction and discarding this one.
The timestamps of the logs tell that it takes 5 seconds for this host
to get out of the ALTER DATABASE .. RENAME TO, which implies that we
are looping inside CountOtherDBBackends() for 5 seconds. So it really
looks like the second bgworker is the one we are waiting for here.
Now, we are sure of the following things when we try to launch the
RENAME TO:
- The worker is seen in pg_stat_activity.
- The worker is already in worker_spi_main(), per its "LOG initialized
with" entry.
- The worker is connected to the database.
- The worker can receive signals.
How would it be possible for this worker to not receive the requests?
The only thing I could think of is that the postmaster does not have
the time to process the PMSIGNAL_BACKGROUND_WORKER_CHANGE requests?
The next thing would be to gather more data, I guess. The attached
would help in providing more information. If it happens that we are
able to send the requests and that the postmaster does not have the
time to process them, I don't really see what we can do except:
- Drop the portion of the tests for DROP DATABASE, SET TABLESPACE and
RENAME DB, because all these scenarios involve commands that work on
the same database as the worker connected, and if the postmaster does
not have the time to process the termination requests, I don't really
see what we could do. This could also point to a timing issue with
the feature in itself, of course.
- Revert the feature, stop playing with the buildfarm due to the end
of the release cycle, and rework it for v20.
For now I am planning for the attached to get more information from
widowbird, which should take a few days at worst. That would make
clear if we have a timing issue with the requests sent to the
postmaster. Launching the queries for worker_spi_launch() and
pg_stat_activity on the database postgres may also improve things, but
I don't really buy it, even if I may be wrong.
[1]: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2026-03-17%2015%3A35%3A03
--
Michael
From adb474866f12a66ac48c702d9ab25ce82277abca Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Thu, 19 Mar 2026 09:51:16 +0900
Subject: [PATCH] Add more debugging information for termination tests of
worker_spi
---
src/backend/postmaster/bgworker.c | 6 ++++++
src/test/modules/worker_spi/t/002_worker_terminate.pl | 10 ++++++++--
2 files changed, 14 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/bgworker.c b/src/backend/postmaster/bgworker.c
index 0104a86b9ecd..fd678ef2596d 100644
--- a/src/backend/postmaster/bgworker.c
+++ b/src/backend/postmaster/bgworker.c
@@ -1413,6 +1413,9 @@ TerminateBackgroundWorkersForDatabase(Oid databaseId)
{
bool signal_postmaster = false;
+ elog(DEBUG1, "attempting worker termination for database %u",
+ databaseId);
+
LWLockAcquire(BackgroundWorkerLock, LW_EXCLUSIVE);
/*
@@ -1432,6 +1435,9 @@ TerminateBackgroundWorkersForDatabase(Oid databaseId)
{
slot->terminate = true;
signal_postmaster = true;
+
+ elog(DEBUG1, "termination requested for worker (PID %d) on database %u",
+ slot->pid, databaseId);
}
}
}
diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/test/modules/worker_spi/t/002_worker_terminate.pl
index 6db80ffec88c..b0e6a5376d4c 100644
--- a/src/test/modules/worker_spi/t/002_worker_terminate.pl
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -24,7 +24,7 @@ sub launch_bgworker
# Launch a background worker on the given database.
my $pid = $node->safe_psql(
- $database, qq(
+ 'postgres', qq(
SELECT worker_spi_launch($testcase, '$database'::regdatabase, 0, '{}', $interruptible);
));
@@ -32,7 +32,7 @@ sub launch_bgworker
$node->wait_for_log(
qr/LOG: .*worker_spi dynamic worker $testcase initialized with .*\..*/,
$offset);
- my $result = $node->safe_psql($database,
+ my $result = $node->safe_psql('postgres',
"SELECT count(*) > 0 FROM pg_stat_activity WHERE pid = $pid;");
is($result, 't', "dynamic bgworker $testcase launched");
@@ -52,6 +52,11 @@ sub run_bgworker_interruptible_test
qr/terminating background worker \"worker_spi dynamic\" due to administrator command/,
$offset);
+ # Postmaster entry reporting the worker as exiting.
+ $node->wait_for_log(
+ qr/LOG: .*background worker \"worker_spi dynamic\" \(PID $pid\) exited with exit code/,
+ $offset);
+
my $result = $node->safe_psql('postgres',
"SELECT count(*) = 0 FROM pg_stat_activity WHERE pid = $pid;");
is($result, 't', "dynamic bgworker stopped for $testname");
@@ -63,6 +68,7 @@ $node->append_conf(
"postgresql.conf", qq(
autovacuum = off
debug_parallel_query = off
+log_min_messages = debug1
));
$node->start;
--
2.53.0
Attachments:
[text/plain] 0001-Add-more-debugging-information-for-termination-tests.patch (2.7K, 2-0001-Add-more-debugging-information-for-termination-tests.patch)
download | inline diff:
From adb474866f12a66ac48c702d9ab25ce82277abca Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Thu, 19 Mar 2026 09:51:16 +0900
Subject: [PATCH] Add more debugging information for termination tests of
worker_spi
---
src/backend/postmaster/bgworker.c | 6 ++++++
src/test/modules/worker_spi/t/002_worker_terminate.pl | 10 ++++++++--
2 files changed, 14 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/bgworker.c b/src/backend/postmaster/bgworker.c
index 0104a86b9ecd..fd678ef2596d 100644
--- a/src/backend/postmaster/bgworker.c
+++ b/src/backend/postmaster/bgworker.c
@@ -1413,6 +1413,9 @@ TerminateBackgroundWorkersForDatabase(Oid databaseId)
{
bool signal_postmaster = false;
+ elog(DEBUG1, "attempting worker termination for database %u",
+ databaseId);
+
LWLockAcquire(BackgroundWorkerLock, LW_EXCLUSIVE);
/*
@@ -1432,6 +1435,9 @@ TerminateBackgroundWorkersForDatabase(Oid databaseId)
{
slot->terminate = true;
signal_postmaster = true;
+
+ elog(DEBUG1, "termination requested for worker (PID %d) on database %u",
+ slot->pid, databaseId);
}
}
}
diff --git a/src/test/modules/worker_spi/t/002_worker_terminate.pl b/src/test/modules/worker_spi/t/002_worker_terminate.pl
index 6db80ffec88c..b0e6a5376d4c 100644
--- a/src/test/modules/worker_spi/t/002_worker_terminate.pl
+++ b/src/test/modules/worker_spi/t/002_worker_terminate.pl
@@ -24,7 +24,7 @@ sub launch_bgworker
# Launch a background worker on the given database.
my $pid = $node->safe_psql(
- $database, qq(
+ 'postgres', qq(
SELECT worker_spi_launch($testcase, '$database'::regdatabase, 0, '{}', $interruptible);
));
@@ -32,7 +32,7 @@ sub launch_bgworker
$node->wait_for_log(
qr/LOG: .*worker_spi dynamic worker $testcase initialized with .*\..*/,
$offset);
- my $result = $node->safe_psql($database,
+ my $result = $node->safe_psql('postgres',
"SELECT count(*) > 0 FROM pg_stat_activity WHERE pid = $pid;");
is($result, 't', "dynamic bgworker $testcase launched");
@@ -52,6 +52,11 @@ sub run_bgworker_interruptible_test
qr/terminating background worker \"worker_spi dynamic\" due to administrator command/,
$offset);
+ # Postmaster entry reporting the worker as exiting.
+ $node->wait_for_log(
+ qr/LOG: .*background worker \"worker_spi dynamic\" \(PID $pid\) exited with exit code/,
+ $offset);
+
my $result = $node->safe_psql('postgres',
"SELECT count(*) = 0 FROM pg_stat_activity WHERE pid = $pid;");
is($result, 't', "dynamic bgworker stopped for $testname");
@@ -63,6 +68,7 @@ $node->append_conf(
"postgresql.conf", qq(
autovacuum = off
debug_parallel_query = off
+log_min_messages = debug1
));
$node->start;
--
2.53.0
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-19 00:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-21 02:46 ` Michael Paquier <[email protected]>
2026-03-31 07:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-21 02:46 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Alexander Lakhin <[email protected]>; Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
On Thu, Mar 19, 2026 at 09:54:04AM +0900, Michael Paquier wrote:
> For now I am planning for the attached to get more information from
> widowbird, which should take a few days at worst. That would make
> clear if we have a timing issue with the requests sent to the
> postmaster. Launching the queries for worker_spi_launch() and
> pg_stat_activity on the database postgres may also improve things, but
> I don't really buy it, even if I may be wrong.
A couple of days later after 79a5911fe65b, widowbird seems to have
cooled down a bit:
https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=widowbird&br=master
The extra debugging information is proving to be useful. For example,
looking at this one for the RENAME TO case:
https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=widowbird&dt=2026-03-19%2003%3A35%...
And some problematic pattern logs:
2026-03-19 04:49:07.848 UTC [271557:4] 002_worker_terminate.pl LOG:
statement: ALTER DATABASE testdb RENAME TO renameddb
2026-03-19 04:49:07.849 UTC [271557:5] 002_worker_terminate.pl DEBUG:
attempting worker termination for database 16413
2026-03-19 04:49:07.849 UTC [271557:6] 002_worker_terminate.pl DEBUG:
termination requested for worker (PID 271553) on database 16413
[...]
2026-03-19 04:49:08.732 UTC [271557:19] 002_worker_terminate.pl DEBUG:
attempting worker termination for database 16413
2026-03-19 04:49:08.732 UTC [271557:20] 002_worker_terminate.pl DEBUG:
termination requested for worker (PID 271553) on database 16413
So we are able to send the requests to the workers, and these can take
a long time before being processed by the postmaster. Querying
directly "postgres" for the worker_spi_launch() and pg_stat_activity
queries seems to have reduced the friction, with less requests to
send. However, I don't think that this is the end of the story, even
after 79a5911fe65b I have spotted one case of RENAME TO where the
requests were sent for a bit more than 4s, before the postmaster had
the idea to catch up. RENAME TO is the only one that can get slow
(really no idea why), so I guess that we could always tweak things a
bit more:
1) Extra injection point to increase the timeout (30s or 60s?) and
give the postmaster more room to proceed the requests.
2) Remove this portion of the test, but it would be sad.
I'll keep an eye for more failures, even if the situation is looking
slightly better.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-19 00:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-21 02:46 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-31 07:00 ` Alexander Lakhin <[email protected]>
2026-03-31 10:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Alexander Lakhin @ 2026-03-31 07:00 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; +Cc: Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
Hello Michael,
21.03.2026 04:46, Michael Paquier wrote:
> So we are able to send the requests to the workers, and these can take
> a long time before being processed by the postmaster. Querying
> directly "postgres" for the worker_spi_launch() and pg_stat_activity
> queries seems to have reduced the friction, with less requests to
> send. However, I don't think that this is the end of the story, even
> after 79a5911fe65b I have spotted one case of RENAME TO where the
> requests were sent for a bit more than 4s, before the postmaster had
> the idea to catch up. RENAME TO is the only one that can get slow
> (really no idea why), so I guess that we could always tweak things a
> bit more:
> 1) Extra injection point to increase the timeout (30s or 60s?) and
> give the postmaster more room to proceed the requests.
> 2) Remove this portion of the test, but it would be sad.
>
> I'll keep an eye for more failures, even if the situation is looking
> slightly better.
Having reproduced this locally (running 3 tests in parallel with
ALTER DATABASE RENAME repeated 200 times, on a slow riscv64 machine), I
discovered that in the bad case the worker doesn't reach the main loop in
time (and CHECK_FOR_INTERRUPTS() inside it), because it doesn't get out of
initialize_worker_spi() -> CommitTransactionCommand().
With this modification:
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3752,3 +3752,3 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
*/
- int ntries = 50;
+ int ntries = 500;
@@ -3798,3 +3798,6 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
if (!found)
+{
+elog(LOG, "!!!CountOtherDBBackends| found no backends, try %d", tries);
return false; /* no conflicting backends, so done */
+}
I can see the following:
... !!!CountOtherDBBackends| found no backends, try 1
# most of the calls (200 of 201) succeeded with try 1, but there are also:
... !!!CountOtherDBBackends| found no backends, try 7
... !!!CountOtherDBBackends| found no backends, try 51
... !!!CountOtherDBBackends| found no backends, try 74
... !!!CountOtherDBBackends| found no backends, try 84
So the backend is not completely stuck, but CommitTransactionCommand()
may take more than 5 seconds under some circumstances (maybe it's worth
investigating which exactly).
Best regards,
Alexander
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-19 00:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-21 02:46 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-31 07:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
@ 2026-03-31 10:54 ` Michael Paquier <[email protected]>
2026-03-31 17:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Michael Paquier @ 2026-03-31 10:54 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: Tom Lane <[email protected]>; Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
On Tue, Mar 31, 2026 at 10:00:00AM +0300, Alexander Lakhin wrote:
> So the backend is not completely stuck, but CommitTransactionCommand()
> may take more than 5 seconds under some circumstances (maybe it's worth
> investigating which exactly).
One could blame slow hardware, difficult to say, and I'm puzzled by
these periodic bumps that don't seem to happen elsewhere. There is at
least one alternative that I can think here to make the test more
stable and make sure that worker_spi reaches its main loop before
attempting the database commands. We could add a wait_for_event() at
the end of launch_bgworker() based on WorkerSpiMain, and enlarge
worker_spi_naptime to an insanely larger value, to make sure that we
remain on the WaitLatch() until the worker is interrupted and that we
don't attempt a new transaction.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Peter Smith <[email protected]>
2026-01-06 05:38 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-13 11:38 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-16 23:37 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 01:55 ` RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-17 06:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-19 00:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-21 02:46 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
2026-03-31 07:00 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Alexander Lakhin <[email protected]>
2026-03-31 10:54 ` Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Michael Paquier <[email protected]>
@ 2026-03-31 17:00 ` Alexander Lakhin <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Alexander Lakhin @ 2026-03-31 17:00 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Iwata, Aya/岩田 彩 <[email protected]>; Peter Smith <[email protected]>; Kuroda, Hayato/黒田 隼人 <[email protected]>; Pavel Stehule <[email protected]>; Chao Li <[email protected]>; pgsql-hackers
31.03.2026 13:54, Michael Paquier wrote:
> On Tue, Mar 31, 2026 at 10:00:00AM +0300, Alexander Lakhin wrote:
>> So the backend is not completely stuck, but CommitTransactionCommand()
>> may take more than 5 seconds under some circumstances (maybe it's worth
>> investigating which exactly).
> One could blame slow hardware, difficult to say, and I'm puzzled by
> these periodic bumps that don't seem to happen elsewhere.
I managed to get the backtrace of such a sluggish backend:
Using host libthread_db library "/lib/riscv64-linux-gnu/libthread_db.so.1".
0x0000003fb1f4cc26 in posix_fadvise64 () from /lib/riscv64-linux-gnu/libc.so.6
Id Target Id Frame
* 1 Thread 0x3fb2a4c620 (LWP 564194) "postgres" 0x0000003fb1f4cc26 in posix_fadvise64 () from
/lib/riscv64-linux-gnu/libc.so.6
#0 0x0000003fb1f4cc26 in posix_fadvise64 () from /lib/riscv64-linux-gnu/libc.so.6
#1 0x0000002abef79444 in XLogFileClose () at xlog.c:3672
#2 0x0000002abef7cc66 in XLogWrite (WriteRqst=..., tli=tli@entry=1, flexible=flexible@entry=false) at xlog.c:2356
#3 0x0000002abef7dbfc in XLogFlush (record=33561688) at xlog.c:2892
#4 0x0000002abef77976 in RecordTransactionCommit () at xact.c:1516
#5 CommitTransaction () at xact.c:2379
#6 0x0000002abef78938 in CommitTransactionCommandInternal () at xact.c:3224
#7 0x0000002abef78acc in CommitTransactionCommand () at xact.c:3185
#8 0x0000003fb2a3ed88 in initialize_worker_spi (table=0x2abf8bf358) at worker_spi.c:132
#9 worker_spi_main (main_arg=<optimized out>) at worker_spi.c:181
....
(Three test runs produced the same stack trace.)
I think this can explain slow CommitTransactionCommand() and why it
happens not every time. Regarding other animals, I guess they can
experience the same bumps but not exceeding 5 seconds (50 tries). Thus,
from my understanding, for the failure to happen, we need to have slow
storage and initialize_worker_spi() -> CommitTransactionCommand() reaching
XLogFileClose().
Best regards,
Alexander
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-03-31 17:00 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-05 15:06 RE: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE Aya Iwata (Fujitsu) <[email protected]>
2026-01-05 21:18 ` Peter Smith <[email protected]>
2026-01-06 05:38 ` Michael Paquier <[email protected]>
2026-03-13 11:38 ` Aya Iwata (Fujitsu) <[email protected]>
2026-03-16 07:23 ` Michael Paquier <[email protected]>
2026-03-16 23:37 ` Michael Paquier <[email protected]>
2026-03-17 01:55 ` =?iso-2022-jp?B?S3Vyb2RhLCBIYXlhdG8vGyRCOXVFRBsoQiAbJEJIOz9NGyhC?= <[email protected]>
2026-03-17 03:57 ` Michael Paquier <[email protected]>
2026-03-17 06:00 ` Alexander Lakhin <[email protected]>
2026-03-17 08:33 ` Michael Paquier <[email protected]>
2026-03-19 00:54 ` Michael Paquier <[email protected]>
2026-03-21 02:46 ` Michael Paquier <[email protected]>
2026-03-31 07:00 ` Alexander Lakhin <[email protected]>
2026-03-31 10:54 ` Michael Paquier <[email protected]>
2026-03-31 17:00 ` Alexander Lakhin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox