Date: Thu, 10 Sep 2026 20:27:00 -0700
From: Noah Misch <noah@leadboat.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Trying to break online checksums with LLMs
Message-ID: <20260911032700.4e.noahmisch@microsoft.com>
References: <E15AC050-C4B5-488D-BB2D-3C7AC9F89EA8@yesql.se>
 <CA+TgmoaOCdjAjr240e_+xoqQCRmLC9MFn3kZOu-7j8w8HBrd8g@mail.gmail.com>
 <2F197F99-9142-481D-8DC9-37FAAF09EC7D@yesql.se>
 <CA+TgmoZiqihva4Udk5+nzOOjOSZnqv1HAjS5=KHoEc9_FL=X=g@mail.gmail.com>
 <3108345D-DC76-4FDA-9F9E-7583AEC81CC3@yesql.se>
 <CA+TgmobUq6-mMph476ZZOn+0SM_YRNijGFg4X4jS5MVL1q+N7Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmobUq6-mMph476ZZOn+0SM_YRNijGFg4X4jS5MVL1q+N7Q@mail.gmail.com>
User-Agent: Mutt/2.3.0 (2026-01-25)
Archived-At: 
 <https://www.postgresql.org/message-id/20260911032700.4e.noahmisch%40microsoft.com>
Precedence: bulk

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@yesql.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.