Re: Trying to break online checksums with LLMs

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.

--
Robert Haas