From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <A2681026-3AE4-4F3E-9BD7-5F78DF2D1E34@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_D05460DC-2BDB-446E-A03D-C60D2A8A3584"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.100.1.1.5\))
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
Date: Wed, 1 Oct 2025 14:15:06 +0200
In-Reply-To: 
 <CAKZiRmwHKY=KTjBEL3S2cVQpo1OjHyky4BdgJm4Hkv1-ig9PfQ@mail.gmail.com>
Cc: Michael Banck <mbanck@gmx.net>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Aleksander Alekseev <aleksander@tigerdata.com>,
 pgsql-hackers@lists.postgresql.org
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
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>
 <CAKZiRmwHKY=KTjBEL3S2cVQpo1OjHyky4BdgJm4Hkv1-ig9PfQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/A2681026-3AE4-4F3E-9BD7-5F78DF2D1E34%40gmail.com>
Precedence: bulk


--Apple-Mail=_D05460DC-2BDB-446E-A03D-C60D2A8A3584
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Thank you for looking into this Jakub, Aleksander, Michael, Tom,

There are more scenario's where files might be missing: how about a =
backup somehow missing files?
But also deliberate sabotage, you can hide data in this way, and it =
would be hard to detect, and even
harder to understand what happened, why it happened and how it happened. =
I dare to say quite ideal
if you want a name to be removed from a database a covert way, and know =
something about the
database and have access, or somehow can manipulate in the data =
directory.

Without having the exact sourcecode files and functions handy, the way I =
think using the segments
works in postgres, is that the database, because of the absence of any =
indicator of size, will scan=20
the relfilenode, and when it reaches the set limit (1GB), it will try to =
open the next segment. If that=20
segment doesn't exist, the database assumes it's the end of the segment. =
That is all. That is why there
is no error: it's exactly alike when there truly is no more data, and =
there is no metadata to understand
there is supposed to be more data.

And therefore I talked about the primary key: that contains "indirect" =
data to be able to detect if a
table has a missing segment. But I think really what is needed is a max =
page number to indicate
the known size.


Frits Hoogland


> On 1 Oct 2025, at 13:20, Jakub Wartak <jakub.wartak@enterprisedb.com> =
wrote:
>=20
> On Wed, Oct 1, 2025 at 9:02=E2=80=AFAM Michael Banck <mbanck@gmx.net> =
wrote:
>>=20
>> Hi,
>>=20
>> wow, this is one of the most terrifying threads I've ever seen...
>=20
> Same.
>=20
>> 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.
>>>=20
>>>> ... 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.
>>>=20
>>> As Frits mentioned, neither of those actions will really notice if a
>>> table has been truncated via loss of a segment.
>>=20
>> Is there a valid case for a missing segment? If not, couldn't this be
>> caught somewhere in the storage manager?
>>=20
>=20
> 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()->smgrnblock=
s()->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.
>=20
> 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.
>=20
> 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 ;)
>=20
> -J.
>=20
> [1] - =
https://www.postgresql.org/message-id/flat/CA%2BhUKG%2BBGXwMbrvzXAjL8VMGf2=
5y_ga_XnO741g10y0%3Dm6dDiA%40mail.gmail.com


--Apple-Mail=_D05460DC-2BDB-446E-A03D-C60D2A8A3584
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html aria-label=3D"message body"><head><meta http-equiv=3D"content-type" =
content=3D"text/html; charset=3Dutf-8"></head><body =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;">Thank you for looking into this Jakub, =
Aleksander, Michael, Tom,<div><br></div><div>There are more scenario's =
where files might be missing: how about a backup somehow missing =
files?</div><div>But also deliberate sabotage, you can hide data in this =
way, and it would be hard to detect, and even</div><div>harder to =
understand what happened, why it happened and how it happened. I dare to =
say quite ideal</div><div>if you want a name to be removed from a =
database a covert way, and know something about the</div><div>database =
and have access, or somehow can manipulate in the data =
directory.</div><div><br></div><div>Without having the exact sourcecode =
files and functions handy, the way I think using the =
segments</div><div>works in postgres, is that the database, because of =
the absence of any indicator of size, will scan&nbsp;</div><div>the =
relfilenode, and when it reaches the set limit (1GB), it will try to =
open the next segment. If that&nbsp;</div><div>segment doesn't exist, =
the database assumes it's the end of the segment. That is all. That is =
why there</div><div>is no error: it's exactly alike when there truly is =
no more data, and there is no metadata to understand</div><div>there is =
supposed to be more data.</div><div><br></div><div>And therefore I =
talked about the primary key: that contains "indirect" data to be able =
to detect if a</div><div>table has a missing segment. But I think really =
what is needed is a max page number to indicate</div><div>the known =
size.</div><div><br></div><div><br></div><div><br =
id=3D"lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div><b>Frits =
Hoogland</b></div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<div><br><blockquote type=3D"cite"><div>On 1 Oct 2025, at 13:20, Jakub =
Wartak &lt;jakub.wartak@enterprisedb.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>On Wed, Oct 1, 2025 at =
9:02=E2=80=AFAM Michael Banck &lt;mbanck@gmx.net&gt; =
wrote:<br><blockquote type=3D"cite"><br>Hi,<br><br>wow, this is one of =
the most terrifying threads I've ever =
seen...<br></blockquote><br>Same.<br><br><blockquote type=3D"cite">On =
Tue, Sep 30, 2025 at 12:41:29PM -0400, Tom Lane wrote:<br><blockquote =
type=3D"cite">Aleksander Alekseev &lt;aleksander@tigerdata.com&gt; =
writes:<br><blockquote type=3D"cite"><blockquote type=3D"cite">Therefore, =
I would like to request an enhancement: add an option =
to<br>verify_heapam() that causes the primary key index to be scanned =
and makes<br>sure that all line pointers in the index point to existing =
tuples.<br></blockquote></blockquote><br><blockquote type=3D"cite">... =
IMO there is little value in adding a check for the existence of<br>the =
segments for a single table. And the *real* check will not =
differ<br>much from something like SELECT * FROM my_table, or from =
making a<br>complete backup of the database.<br></blockquote><br>As =
Frits mentioned, neither of those actions will really notice if =
a<br>table has been truncated via loss of a =
segment.<br></blockquote><br>Is there a valid case for a missing =
segment? If not, couldn't this be<br>caught somewhere in the storage =
manager?<br><br></blockquote><br>I've took a look on PG17 and in =
_mfd_openseg() there's if fd &lt; 0<br>return NULL after open(), but out =
of it's callers only _mdfd_getseg()<br>seems to be alerting on that =
NULL. To me this seems like a bug,<br>because i've seen way too many =
times people and software deleting<br>files randomly. Even simple =
crashes (with e2fsck, xfs_repair) could<br>put orphaned inodes into =
/lost+found. IMHO all files should be opened<br>at least on startup to =
check integrity, because the non-zero return<br>code (during such =
SELECT) for openat(2) seems o be coming out =
of<br>RelationGetNumberOfBlocksInFork()-&gt;table_block_relation_size()-&g=
t;smgrnblocks()-&gt;mdnblocks()-&gt;_mdfd_openseg().<br>Now if the 1st =
seg file would be missing we would complain in<br>mdopenfork(). =
mdnblocks() says even "all active segments of the<br>relation are =
opened...", but even that apparently is not true.<br><br>The bigger =
context seems to be be that 049469e7e7cfe0c69 (2015) could<br>be culprit =
here as well, as it is stated there that mdnblocks() could<br>earlier =
create zero-length files back in day and it removed =
that<br>ereport(ERROR) when unable to access that file.<br><br>Another =
idea (than this being a bug) is that Thomas had a large<br>relation =
patchset back in [1], but I wouldn't be a fan of us operating<br>on =
31-32TB files ;)<br><br>-J.<br><br>[1] - =
https://www.postgresql.org/message-id/flat/CA%2BhUKG%2BBGXwMbrvzXAjL8VMGf2=
5y_ga_XnO741g10y0%3Dm6dDiA%40mail.gmail.com<br></div></div></blockquote></=
div><br></div></body></html>=

--Apple-Mail=_D05460DC-2BDB-446E-A03D-C60D2A8A3584--