Message-ID: <361531e2-52b5-499c-a126-815f277bbef2@gmail.com>
Date: Tue, 18 Aug 2026 10:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: datachecksums: handle invalid and dropped databases during enable
To: Daniel Gustafsson <daniel@yesql.se>, Tomas Vondra <tomas@vondra.me>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>,
 "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CAN4CZFOGdqxtZ5-6gb4apqmvoH=Z+TNH8RKJ3mVtoR1HirKQWg@mail.gmail.com>
 <AC95ADBB-9D18-4A4E-9939-4154BCA56341@yesql.se>
 <28CF6FD9-E1C4-4C04-8270-E3305AC46171@yesql.se> <87pl049996.fsf@gmail.com>
 <38651C03-7582-44AD-9895-5784755E9E7C@yesql.se>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <38651C03-7582-44AD-9895-5784755E9E7C@yesql.se>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/361531e2-52b5-499c-a126-815f277bbef2%40gmail.com>
Precedence: bulk

Hello Daniel and Tomas,

30.07.2026 14:02, Daniel Gustafsson wrote:
>> On 30 Jul 2026, at 10:23, Jonathan Gonzalez V. <jonathan.abdiel@gmail.com> wrote:
>> Daniel Gustafsson <daniel@yesql.se> writes:
>> I've tested your patch and confirm that it fixed the issue
> Thanks for review, pushed and backpatched.

It looks like that that addition to test_checksums/001_basic.pl is not
very stable on machines having slow storage. Buildfarm animal turaco
(Raspberry Pi, presumably with SD card onboard) fails the test pretty
reliably, e.g., [1], [2]:
[21:37:30.016](0.002s) # issuing query 1 via background psql: CREATE TEMP TABLE holdme (a int);
[21:37:30.024](0.009s) # results query 1:
# {
#   'stderr' => 'background_psql: QUERY_SEPARATOR 1:
# ',
#   'stdout' => 'background_psql: QUERY_SEPARATOR 1:
# '
# }
[21:37:47.284](17.259s) # die: error running SQL: 'psql:<stdin>:1: ERROR:  database "dropmeforce" 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=12278 
host=/mnt/data/buildfarm/buildroot/tmp/n3IU4kJupg dbname='postgres' --file - --variable ON_ERROR_STOP=1' with sql 'DROP 
DATABASE dropmeforce WITH (FORCE);' at t/001_basic.pl line 163.
[21:37:47.286](0.002s) 1..19
error running SQL: 'psql:<stdin>:1: ERROR:  database "dropmeforce" 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=12278 
host=/mnt/data/buildfarm/buildroot/tmp/n3IU4kJupg dbname='postgres' --file - --variable ON_ERROR_STOP=1' with sql 'DROP 
DATABASE dropmeforce WITH (FORCE);' at t/001_basic.pl line 163.

Probably, it's caused by the storage degradation (the test case was
committed on 2026-07-28, but the first failure was produced on 2026-08-13)
and the given test is just the most demanding (only this test fails there)
for now, but I can reproduce such failures on Orange PI with SD card too,
running 1-3 test instances simultaneously. With the diagnostic logging
added:
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3787,6 +3787,7 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
                         else
                         {
                                 (*nbackends)++;
+fprintf(stderr, "!!!CountOtherDBBackends[%d]| tries: %d, proc-pid: %d\n", getpid(), tries, proc->pid);
                                 if ((statusFlags & PROC_IS_AUTOVACUUM) &&
                                         nautovacs < MAXAUTOVACPIDS)
                                         autovac_pids[nautovacs++] = proc->pid;

I can see the following counts when the test passes:
...
!!!CountOtherDBBackends[129416]| tries: 45, proc-pid: 129391
!!!CountOtherDBBackends[129416]| tries: 46, proc-pid: 129391
!!!CountOtherDBBackends[129416]| tries: 47, proc-pid: 129391

But the count can also reach 50, then the test fails as below:
...
2026-08-18 06:01:56.058 UTC client backend[170284] 001_basic.pl FATAL:  terminating connection due to administrator command
2026-08-18 06:01:56.058 UTC client backend[170284] 001_basic.pl DETAIL:  Signal sent by PID 170453, UID 1001.
2026-08-18 06:01:56.058 UTC datachecksums worker[170446] FATAL: terminating connection due to administrator command
2026-08-18 06:01:56.058 UTC datachecksums worker[170446] DETAIL: Signal sent by PID 170453, UID 1001.
2026-08-18 06:01:56.071 UTC postmaster[169264] LOG:  background worker "datachecksums worker" (PID 170446) exited with 
exit code 1
!!!CountOtherDBBackends[170453]| tries: 1, proc-pid: 170284
!!!CountOtherDBBackends[170453]| tries: 2, proc-pid: 170284
...
!!!CountOtherDBBackends[170453]| tries: 47, proc-pid: 170284
!!!CountOtherDBBackends[170453]| tries: 48, proc-pid: 170284
!!!CountOtherDBBackends[170453]| tries: 49, proc-pid: 170284
2026-08-18 06:02:03.632 UTC client backend[170453] 001_basic.pl ERROR:  database "dropmeforce" is being accessed by 
other users
2026-08-18 06:02:03.632 UTC client backend[170453] 001_basic.pl DETAIL:  There is 1 other session using the database.
2026-08-18 06:02:03.632 UTC client backend[170453] 001_basic.pl STATEMENT:  DROP DATABASE dropmeforce WITH (FORCE);

Backtrace of the stuck backend 170284:
   Id   Target Id                                   Frame
* 1    Thread 0x3fa7dd6020 (LWP 170284) "postgres" 0x0000003fa72ff1c4 in ?? () from /lib/riscv64-linux-gnu/libc.so.6
#0  0x0000003fa72ff1c4 in ?? () from /lib/riscv64-linux-gnu/libc.so.6
#1  0x0000003fa7308fa0 in ?? () from /lib/riscv64-linux-gnu/libc.so.6
#2  0x0000002abde863a6 in PGSemaphoreLock (sema=0x3fa46f3980) at pg_sema.c:320
#3  0x0000002abdf8a858 in LWLockAcquireOrWait (lock=0x3f9b6e6b80, mode=LW_EXCLUSIVE) at lwlock.c:1434
#4  0x0000002abdaaba08 in XLogFlush (record=188513088) at xlog.c:2874
#5  0x0000002abdaa1250 in RecordTransactionCommit () at xact.c:1544
#6  0x0000002abdaa2246 in CommitTransaction () at xact.c:2406
#7  0x0000002abdaa3150 in CommitTransactionCommandInternal () at xact.c:3254
#8  0x0000002abdaa3080 in CommitTransactionCommand () at xact.c:3215
#9  0x0000002abdb0ab44 in RemoveTempRelationsCallback (code=1, arg=0) at namespace.c:4708
#10 0x0000002abdf6290c in shmem_exit (code=1) at ipc.c:250
#11 0x0000002abdf6279a in proc_exit_prepare (code=1) at ipc.c:199
#12 0x0000002abdf626d0 in proc_exit (code=1) at ipc.c:112
#13 0x0000002abe1a2ab4 in errfinish (filename=0x2abe40ddd0 "postgres.c", lineno=3518, funcname=0x2abe40fd80 <__func__.6> 
"ProcessInterrupts") at elog.c:608
#14 0x0000002abdfab748 in ProcessInterrupts () at postgres.c:3518
#15 0x0000002abdfa7248 in ProcessClientReadInterrupt (blocked=true) at postgres.c:522
#16 0x0000002abdd40de2 in secure_read (port=0x2abe6ee580, ptr=0x2abe696df0 <PqRecvBuffer>, len=8192) at be-secure.c:251
#17 0x0000002abdd4a47a in pq_recvbuf () at pqcomm.c:921
#18 0x0000002abdd4a56c in pq_getbyte () at pqcomm.c:967
#19 0x0000002abdfa6f0e in SocketBackend (inBuf=0x3fcb500e08) at postgres.c:376
#20 0x0000002abdfa71de in ReadCommand (inBuf=0x3fcb500e08) at postgres.c:499
#21 0x0000002abdfad88a in PostgresMain (dbname=0x2abe76ce48 "dropmeforce", username=0x2abe76ce28 "orangepi") at 
postgres.c:4878
#22 0x0000002abdfa2ffa in BackendMain (startup_data=0x3fcb501040, startup_data_len=24) at backend_startup.c:124
#23 0x0000002abde94d0c in postmaster_child_launch (child_type=B_BACKEND, child_slot=1, startup_data=0x3fcb501040, 
startup_data_len=24, client_sock=0x3fcb501098) at launch_backend.c:268
#24 0x0000002abde9b85a in BackendStartup (client_sock=0x3fcb501098) at postmaster.c:3627
#25 0x0000002abde98bb0 in ServerLoop () at postmaster.c:1728
#26 0x0000002abde984ba in PostmasterMain (argc=4, argv=0x2abe6ec9e0) at postmaster.c:1415
#27 0x0000002abdd53380 in main (argc=4, argv=0x2abe6ec9e0) at main.c:231

Thus, it's not the checksum worker preventing the DROP, but 001_basic.pl
session.

This seems similar to [3], though this time the animal and the code path
are different.

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=turaco&dt=2026-08-13%2021%3A07%3A46
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=turaco&dt=2026-08-15%2019%3A27%3A11
[3] https://www.postgresql.org/message-id/ab4Gf2r-Bd6P7BiW%40paquier.xyz

Best regards,
Alexander