MIME-Version: 1.0
References: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com>
 <CAJ7c6TMYvYBQpgv8k+4QnajqPcroOzrj7d3snBBTKX4tHQfVYw@mail.gmail.com>
 <499686.1759250489@sss.pgh.pa.us>
 <68dcd1f2.df0a0220.3300c0.f7af@mx.google.com>
In-Reply-To: <68dcd1f2.df0a0220.3300c0.f7af@mx.google.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 1 Oct 2025 13:20:48 +0200
Message-ID: 
 <CAKZiRmwHKY=KTjBEL3S2cVQpo1OjHyky4BdgJm4Hkv1-ig9PfQ@mail.gmail.com>
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
To: Michael Banck <mbanck@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Aleksander Alekseev <aleksander@tigerdata.com>,
	Frits Hoogland <frits.hoogland@gmail.com>, pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAKZiRmwHKY%3DKTjBEL3S2cVQpo1OjHyky4BdgJm4Hkv1-ig9PfQ%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 1, 2025 at 9:02=E2=80=AFAM Michael Banck <mbanck@gmx.net> wrote=
:
>
> Hi,
>
> wow, this is one of the most terrifying threads I've ever seen...

Same.

> On Tue, Sep 30, 2025 at 12:41:29PM -0400, Tom Lane wrote:
> > Aleksander Alekseev <aleksander@tigerdata.com> writes:
> > >> Therefore, I would like to request an enhancement: add an option to
> > >> verify_heapam() that causes the primary key index to be scanned and =
makes
> > >> sure that all line pointers in the index point to existing tuples.
> >
> > > ... IMO there is little value in adding a check for the existence of
> > > the segments for a single table. And the *real* check will not differ
> > > much from something like SELECT * FROM my_table, or from making a
> > > complete backup of the database.
> >
> > As Frits mentioned, neither of those actions will really notice if a
> > table has been truncated via loss of a segment.
>
> Is there a valid case for a missing segment? If not, couldn't this be
> caught somewhere in the storage manager?
>

I've took a look on PG17 and in _mfd_openseg() there's if fd < 0
return NULL after open(), but out of it's callers only _mdfd_getseg()
seems to be alerting on that NULL. To me this seems like a bug,
because i've seen way too many times people and software deleting
files randomly. Even simple crashes (with e2fsck, xfs_repair) could
put orphaned inodes into /lost+found. IMHO all files should be opened
at least on startup to check integrity, because the non-zero return
code (during such SELECT) for openat(2) seems o be coming out of
RelationGetNumberOfBlocksInFork()->table_block_relation_size()->smgrnblocks=
()->mdnblocks()->_mdfd_openseg().
Now if the 1st seg file would be missing we would complain in
mdopenfork(). mdnblocks() says even "all active segments of the
relation are opened...", but even that apparently is not true.

The bigger context seems to be be that 049469e7e7cfe0c69 (2015) could
be culprit here as well, as it is stated there that mdnblocks() could
earlier create zero-length files back in day and it removed that
ereport(ERROR) when unable to access that file.

Another idea (than this being a bug) is that Thomas had a large
relation patchset back in [1], but I wouldn't be a fan of us operating
on 31-32TB files ;)

-J.

[1] - https://www.postgresql.org/message-id/flat/CA%2BhUKG%2BBGXwMbrvzXAjL8=
VMGf25y_ga_XnO741g10y0%3Dm6dDiA%40mail.gmail.com