On Thu, Sep 10, 2026 at 06:00:31PM -0400, Robert Haas wrote: > On Thu, Sep 10, 2026 at 5:50 PM Daniel Gustafsson <[email protected]> wrote: > > It is too close to midnight for investigation, so I am mostly asking an open > > question as a TODO marker for myself in the morning: Are we guaranteed > > that a > > create relation that crash will end up with orphan files containing correct > > checksums? That is, if you have a cluster initdb'd with checksums on, and > > never use neither online checksums or pg_checksums, can you end with an > > orphan > > file from CREATE TABLE crashing which cause a backup page verification > > failure? > > All of the blocks in an orphaned relation file were properly > WAL-logged when they were written, so they might be inconsistent at > the moment of the crash, but crash replay will fix them up so that > they have valid contents. From replay's point of view, those files > aren't orphaned at all, and it treats them just like any other > relation files. The problem is just that at a certain point they stop > being mentioned in the WAL stream any more, because the transaction > that caused them to be created gets terminated by the system going > down, and never has a chance to write additional WAL records asking > for those files to be removed, nor on the other hand to commit so that > the catalog rows that point to those files become visible. > > Over time, as activity continues in the rest of the cluster but not in > the orphaned files, the xmin and xmax values in any tuples in those > orphaned files will drift away from what the rest of the cluster is > doing -- the *tuples* won't be readable any more -- but the *pages* > are fine. To base backups and anything else that operates at the > storage layer, they just look like pages we haven't chosen to write > for a long while.
Offhand, I expect checksum violations in orphan files can arise with wal_level=minimal. If the transaction never reaches commit, or its commit takes the smgrDoPendingSyncs() -> smgrdosyncall() path, the new relfilenode has no WAL. wal_level=minimal was last the default in v9.6, well before checksums were on by default. Also, a checksum failure in pg_basebackup would require raising wal_level after making the orphan. Those prerequisites may explain the lack of reports.
