datachecksums: handle invalid and dropped databases during enable

On 28 Jul 2026, at 12:01, Zsolt Parragi <zsolt.parragi@percona.com> wrote:

While testing the online enabling of datachecksums, I discovered some
corner cases that can cause the background process to fail:

* Invalid databases were only discovered when we tried to create
checksums for them, when we could instead discover them before
starting and fail early
* Databases dropped while the checksum process was running also
resulted in errors when it reached them and tried to build checksums
for them. In this case we could continue safely: the database was
dropped, there's no need to create checksums for it.

I attached a patch that adds testcases for these and fixes the issues.

Thanks, I've reviewed and tested your patch and intend to push it along with
your other earlier patch shortly.

--
Daniel Gustafsson

Hi,

While checking commit 51f55b13a4d, I noticed a regression in repeated
calls to pg_enable_data_checksums().

Before that commit, calling pg_enable_data_checksums() after checksums had
reached the "on" state succeeded as a no-op. The existing basic TAP test
also documents that behavior.

51f55b13a4d added ErrorOnInvalidDatabases() before
StartDataChecksumsWorkerLauncher(). If an invalid database exists, the
same repeated call now fails even though no database processing is needed:

ERROR: cannot enable data checksums in a cluster with invalid database
"baddb"

The attached v1 patch restores that no-op, but does not use a bare
DataChecksumsOn() early return. Such a return could lose a pending disable
request and violate the existing last-request-wins behavior.

Instead, the patch makes the no-op decision under
DataChecksumsWorkerLock and records the requested enable operation and cost
settings atomically. If a disable operation is already running, it keeps
the invalid-database preflight because the subsequent enable may need to
process every database.

The patch adds two tests. The basic test covers repeated enable with an
invalid database while checksums are on, followed by a real enable from the
off state which must still fail. An injection test pauses an active disable
before the visible state changes from on, and verifies that enable still
checks invalid databases in that case.

Tested on origin/master at
33b392eaabdd1c563d40388784df051821e03c6b:

Unpatched master with the new basic test:
expected FAIL, 1 of 24 tests failed, only the new no-op assertion.

Patched default test_checksums suite:
PASS, 9 files and 95 tests.

Patched extended pgbench, standby, and PITR tests:
PASS, 3 files and 102 tests.

Full build, git diff --check, patch apply check, and Perl syntax:
PASS.

I tested master only. My local REL_19_STABLE ref predates the backpatch of
51f55b13a4d, and I did not refresh it while working offline.

This should be backpatched to v19 together with 51f55b13a4d.

Regards,
Ilmar Yunusov

On 29 Jul 2026, at 14:00, r314tive <tanswis42@gmail.com> wrote:

While checking commit 51f55b13a4d, I noticed a regression in repeated
calls to pg_enable_data_checksums().

Before that commit, calling pg_enable_data_checksums() after checksums had
reached the "on" state succeeded as a no-op. The existing basic TAP test
also documents that behavior.

51f55b13a4d added ErrorOnInvalidDatabases() before
StartDataChecksumsWorkerLauncher(). If an invalid database exists, the
same repeated call now fails even though no database processing is needed:

ERROR: cannot enable data checksums in a cluster with invalid database
"baddb"

I'm not sure that's inherently wrong though, if the precondition for an
operation isn't met then the operation cannot succeed and erroring out is the
right option. If anything, wouldn't it be better to expand the errhint to
mention that any currently running checksum enabling will fail unless the
database is dropped?

The attached v1 patch restores that no-op, but does not use a bare
DataChecksumsOn() early return. Such a return could lose a pending disable
request and violate the existing last-request-wins behavior.

Instead, the patch makes the no-op decision under
DataChecksumsWorkerLock and records the requested enable operation and cost
settings atomically. If a disable operation is already running, it keeps
the invalid-database preflight because the subsequent enable may need to
process every database.

I don't think duplicating this logic from StartDataChecksumsWorkerLauncher is
the right move, enable_data_checksums should IMO only validate parameters and
preconditions and leave the decisionmaking to StartDataChecksumsWorkerLauncher.

--
Daniel Gustafsson

On 28 Jul 2026, at 15:13, Daniel Gustafsson <daniel@yesql.se> wrote:

On 28 Jul 2026, at 12:01, Zsolt Parragi <zsolt.parragi@percona.com> wrote:

I attached a patch that adds testcases for these and fixes the issues.

Thanks, I've reviewed and tested your patch and intend to push it along with
your other earlier patch shortly.

BF member porpoise points out that we seems to have missed a detach in the
injection points test:

# die: error running SQL: 'psql:<stdin>:1: ERROR: injection point "datachecksumsworker-fake-temptable-wait" already defined'
# while running 'psql --no-psqlrc --no-align --tuples-only --quiet --dbname port=19905 host=/tmp/oQlRAvFxKv dbname='postgres' --file - --variable ON_ERROR_STOP=1' with sql 'SELECT injection_points_attach('datachecksumsworker-fake-temptable-wait','wait');' at /home/buildfarm/pg-buildfarm/builds/HEAD/pgsql/src/test/modules/test_checksums/t/005_injection.pl line 92.
# Looks like your test exited with 29 just after 8.

https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=porpoise&amp;dt=2026-07-30%2007%3A22%3A50&amp;stg=test_checksums-check

It only fails when running the extended tests which is why it didn't fail in CI
or on more BF members (and I clearly forgot to run those tests before pushing).
Does this seem correct to you?

--
Daniel Gustafsson

Hi!

Daniel Gustafsson <daniel@yesql.se> writes:

Thanks, I've reviewed and tested your patch and intend to push it along with
your other earlier patch shortly.

BF member porpoise points out that we seems to have missed a detach in the
injection points test:

# die: error running SQL: 'psql:<stdin>:1: ERROR: injection point "datachecksumsworker-fake-temptable-wait" already defined'
# while running 'psql --no-psqlrc --no-align --tuples-only --quiet --dbname
port=19905 host=/tmp/oQlRAvFxKv dbname='postgres' --file - --variable
ON_ERROR_STOP=1' with sql 'SELECT
injection_points_attach('datachecksumsworker-fake-temptable-wait','wait');' at
/home/buildfarm/pg-buildfarm/builds/HEAD/pgsql/src/test/modules/test_checksums/t/005_injection.pl
line 92.
# Looks like your test exited with 29 just after 8.

https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=porpoise&amp;dt=2026-07-30%2007%3A22%3A50&amp;stg=test_checksums-check

I came here to report exactly the same!

It only fails when running the extended tests which is why it didn't fail in CI
or on more BF members (and I clearly forgot to run those tests before pushing).
Does this seem correct to you?

I've tested your patch and confirm that it fixed the issue

Regards!
--
Jonathan Gonzalez V.
EDB
https://www.enterprisedb.com

On 30 Jul 2026, at 10:23, Jonathan Gonzalez V. <jonathan.abdiel@gmail.com> wrote:
Daniel Gustafsson <daniel@yesql.se> writes:

https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=porpoise&amp;dt=2026-07-30%2007%3A22%3A50&amp;stg=test_checksums-check

I came here to report exactly the same!

It only fails when running the extended tests which is why it didn't fail in CI
or on more BF members (and I clearly forgot to run those tests before pushing).
Does this seem correct to you?

I've tested your patch and confirm that it fixed the issue

Thanks for review, pushed and backpatched.

--
Daniel Gustafsson

Hi,

I think there may be another case to consider here. DROP DATABASE durably
marks the database invalid before it removes the catalog row and files, so
a crash or ERROR after the marker is written leaves an invalid row whose
files are still present. If checksum enabling is running at the same
time, the worker will fail to connect to that database, and since
1df361e3d82 DatabaseExists() returns false for an invalid row. AFAICT
the launcher then classifies the failure as a concurrent drop and skips
the database, potentially letting data_checksums reach "on" while those
files were never processed.

AFAICS nothing else rules this out, so I am attaching a patch along with
a TAP test that uses a new injection point in dropdb() to exercise the
race directly. The patch makes DatabaseExists() return true for any
present row; ISTM that AccessShareLock already waits out an in-flight
drop, so an invalid-but-present row can only be from an interrupted one
whose files still need checksums. I am not sure if the patch needs a
dedicated TAP test, however I've added one.

Apologies if this is expected behaviour, and I missed on some thread
mentioning it.

Regards,
Ayush

Apologies if this is expected behaviour, and I missed on some thread
mentioning it.

It was somewhat intentional, but I agree that it is inconsistent and maybe we should improve it.

The original code wanted to skip invalid databases, but also failed because it tried connecting to them before this check. I fixed the connection failure, but I also added a startup check about having no invalid databases, because completing checksum enablement while there are invalid databases didn't seem entirely safe. This seemed like a good compromise because I wanted to avoid surprise process stops, but since a failing drop database happening during the processing is unlikely, handling this properly shouldn't be a real problem.

We could improve this either by adding your patch, or by removing ErrorOnInvalidDatabases, but adding this patch seems to be safer to me.

Hi,

On Fri, 31 Jul 2026 at 03:32, Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Apologies if this is expected behaviour, and I missed on some thread
mentioning it.

It was somewhat intentional, but I agree that it is inconsistent and
maybe we should improve it.

The original code wanted to skip invalid databases, but also failed
because it tried connecting to them before this check. I fixed the
connection failure, but I also added a startup check about having no
invalid databases, because completing checksum enablement while there
are invalid databases didn't seem entirely safe. This seemed like a
good compromise because I wanted to avoid surprise process stops, but
since a failing drop database happening during the processing is
unlikely, handling this properly shouldn't be a real problem.

We could improve this either by adding your patch, or by removing
ErrorOnInvalidDatabases, but adding this patch seems to be safer to
me.

I lean the same way. ISTM the startup check and this change enforce the
same rule from two ends: don't let enablement finish while an invalid
database still has files on disk, whether it was invalid at startup or
turned invalid during processing. Dropping ErrorOnInvalidDatabases would
lose the early, actionable error for the startup case, so keeping it and
handling the mid-run case seems the more consistent option.

I've added a CF entry to track this [1]https://commitfest.postgresql.org/patch/7087/, probably need to change the title.

Regards,
Ayush

[1]: https://commitfest.postgresql.org/patch/7087/

On 3 Aug 2026, at 14:14, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Fri, 31 Jul 2026 at 03:32, Zsolt Parragi <zsolt.parragi@percona.com <mailto:zsolt.parragi@percona.com>> wrote:

We could improve this either by adding your patch, or by removing
ErrorOnInvalidDatabases, but adding this patch seems to be safer to
me.

I lean the same way.

After looking at this and sleeping on it, I think I agree. The case should be
pretty rare to hit but if done this approach seems like the right one. I'll go
make it so, backpatched to 19.

--
Daniel Gustafsson

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]https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=turaco&amp;dt=2026-08-13%2021%3A07%3A46, [2]https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=turaco&amp;dt=2026-08-15%2019%3A27%3A11:
[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]/messages/by-id/ab4Gf2r-Bd6P7BiW@paquier.xyz, though this time the animal and the code path
are different.

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

Best regards,
Alexander

Thanks for the report and analysis!

I managed to reproduce it locally by SIGSTOPping the backend just
before the DROP, it fails exactly after 5 seconds with the same error.

The session terminated by DROP DATABASE ... WITH (FORCE) still holds
the temporary table the test uses to hold the worker in place. During
exit, RemoveTempRelationsCallback drops the temp table in a new
transaction, and the commit of that transaction calls XLogFlush. That
flush queues on WALWriteLock behind the WAL the checksum workers just
generated, and CountOtherDBBackends only waits 50 * 100ms, so on slow
storage DROP DATABASE gives up before the flush
completes.

0001 fixes the test. The background session now uses
synchronous_commit = off, so its exit-time commit does not flush at
all, and the test checkpoints right before the DROP so the few
remaining exit-time WAL inserts cannot queue behind a large backlog.

While reproducing this I found another issue: When DROP fails this
way, it has already terminated the checksums worker, so the launcher
sees a dead worker and a database which still exists, and that cancels
the whole operation and reverts the checksum state to off:

ERROR: database "dropmeforce" is being accessed by other users
...
ERROR: data checksums failed to get enabled in all databases, aborting

The same happens if an administrator simply terminates the worker with
pg_terminate_backend.

0002 makes the launcher retry the database in this situation. The
worker records in shared memory when it is terminated by SIGTERM, and
if the database still exists the launcher starts a new worker for it
instead of aborting.
A worker failing still aborts the operation as before, and canceling
the launcher also still works.