From: Noah Misch <noah@leadboat.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Trying to break online checksums with LLMs
Date: Thu, 10 Sep 2026 20:27:00 -0700
Message-ID: <20260911032700.4e.noahmisch@microsoft.com> (raw)
In-Reply-To: <CA+TgmobUq6-mMph476ZZOn+0SM_YRNijGFg4X4jS5MVL1q+N7Q@mail.gmail.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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: noah@leadboat.com, robertmhaas@gmail.com, daniel@yesql.se, pgsql-hackers@lists.postgresql.org
Subject: Re: Trying to break online checksums with LLMs
In-Reply-To: <20260911032700.4e.noahmisch@microsoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox