Re: Trying to break online checksums with LLMs

On Thu, Sep 10, 2026 at 06:00:31PM -0400, Robert Haas wrote:
> On Thu, Sep 10, 2026 at 5:50 PM Daniel Gustafsson <daniel(at)yesql(dot)se> 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.