Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE

Michael Paquier <michael(at)paquier(dot)xyz> writes:
> 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.

It looks like af8837a10 has made this worse not better, as widowbird
has now failed like this three times since that went in:

sysname | branch | snapshot | stage | l
-----------+--------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------
widowbird | HEAD | 2026-03-17 09:35:02 | TestModulesCheck-C | 2026-03-17 11:02:21.685 UTC [991320:5] 002_worker_terminate.pl ERROR: database "testdb" is being accessed by other users
widowbird | HEAD | 2026-03-17 15:35:03 | TestModulesCheck-C | 2026-03-17 17:31:52.391 UTC [1616016:5] 002_worker_terminate.pl ERROR: database "testdb" is being accessed by other users
widowbird | HEAD | 2026-03-18 14:35:02 | TestModulesCheck-C | 2026-03-18 16:56:12.744 UTC [3466422:5] 002_worker_terminate.pl ERROR: database "testdb" is being accessed by other users

The trace in the TAP test log is

# Postmaster PID for node "mynode" is 1615968
[17:31:45.860](3.725s) ok 1 - dynamic bgworker 0 launched
[17:31:46.262](0.402s) ok 2 - background worker blocked the database creation
[17:31:46.303](0.041s) ok 3 - background worker is still running after CREATE DATABASE WITH TEMPLATE
[17:31:46.546](0.242s) ok 4 - dynamic bgworker 1 launched
[17:31:46.972](0.426s) ok 5 - dynamic bgworker stopped for CREATE DATABASE WITH TEMPLATE
[17:31:47.357](0.385s) ok 6 - dynamic bgworker 2 launched
error running SQL: 'psql:<stdin>:1: ERROR: database "testdb" is being accessed by other users
DETAIL: There is 1 other session using the database.'
while running 'psql --no-psqlrc --no-align --tuples-only --quiet --dbname port=27924 host=/mnt/data/buildfarm/buildroot/tmp/wOIIXD5Xt9 dbname='postgres' --file - --variable ON_ERROR_STOP=1' with sql 'ALTER DATABASE testdb RENAME TO renameddb' at /mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/test/modules/worker_spi/../../../../src/test/perl/PostgreSQL/Test/Cluster.pm line 2300.

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.

regards, tom lane