From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <E769DCEF-8D50-4C97-86F7-52491638DD4F@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_22EDAA34-D917-46C0-BB1A-6E81E34B1BAA"
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:02:50 +0200
In-Reply-To: <499686.1759250489@sss.pgh.pa.us>
Cc: Aleksander Alekseev <aleksander@tigerdata.com>,
 pgsql-hackers@lists.postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com>
 <CAJ7c6TMYvYBQpgv8k+4QnajqPcroOzrj7d3snBBTKX4tHQfVYw@mail.gmail.com>
 <499686.1759250489@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/E769DCEF-8D50-4C97-86F7-52491638DD4F%40gmail.com>
Precedence: bulk


--Apple-Mail=_22EDAA34-D917-46C0-BB1A-6E81E34B1BAA
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Thank you for your answer Tom,

As pointed out in another thread of this topic: using the heapallindexed =
option, it is=20
not possible to detect that the table has missing segments and thus =
missing data.
What it will detect is if the index is missing data that is existing in =
the table, it validates
table->index.

Frits Hoogland


> On 30 Sep 2025, at 18:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> 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
> However, I think the requested functionality already exists via
> contrib/amcheck (see the heapallindexed option).  The user does have
> to make a decision which index to check with, but I think that'd be
> required anyway --- as you say, there isn't necessarily a primary key.
>=20
> 			regards, tom lane


--Apple-Mail=_22EDAA34-D917-46C0-BB1A-6E81E34B1BAA
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html aria-label=3D"message body"><head><meta http-equiv=3D"content-type" =
content=3D"text/html; charset=3Dus-ascii"></head><body =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;">Thank you for your answer =
Tom,<div><br></div><div>As pointed out in another thread of this topic: =
using the heapallindexed option, it is&nbsp;</div><div>not possible to =
detect that the table has missing segments and thus missing =
data.</div><div>What it will detect is if the index is missing data that =
is existing in the table, it =
validates</div><div>table-&gt;index.</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 30 Sep 2025, at 18:41, Tom =
Lane &lt;tgl@sss.pgh.pa.us&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>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><br>However, I =
think the requested functionality already exists via<br>contrib/amcheck =
(see the heapallindexed option). &nbsp;The user does have<br>to make a =
decision which index to check with, but I think that'd be<br>required =
anyway --- as you say, there isn't necessarily a primary =
key.<br><br><span class=3D"Apple-tab-span" style=3D"white-space:pre">	=
</span><span class=3D"Apple-tab-span" style=3D"white-space:pre">	=
</span><span class=3D"Apple-tab-span" style=3D"white-space:pre">	=
</span>regards, tom =
lane<br></div></div></blockquote></div><br></div></body></html>=

--Apple-Mail=_22EDAA34-D917-46C0-BB1A-6E81E34B1BAA--