From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <483AD9AA-39C1-4F4B-B0D6-4CBA0014E877@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_FC3CC28F-4B00-4A0C-8D17-AA21B2ECC544"
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 13:58:54 +0200
In-Reply-To: <45b3a1dd6be28dd409850a5503c029a97ca0754e.camel@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org
To: Laurenz Albe <laurenz.albe@cybertec.at>,
 Aleksander Alekseev <aleksander@tigerdata.com>
References: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com>
 <CAJ7c6TMYvYBQpgv8k+4QnajqPcroOzrj7d3snBBTKX4tHQfVYw@mail.gmail.com>
 <45b3a1dd6be28dd409850a5503c029a97ca0754e.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/483AD9AA-39C1-4F4B-B0D6-4CBA0014E877%40gmail.com>
Precedence: bulk


--Apple-Mail=_FC3CC28F-4B00-4A0C-8D17-AA21B2ECC544
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

I am proposing the database to have the ability to detect when it has =
missing segments.
One of the main and basic properties of a database is to store data =
consistently and reliably.
If the database cannot detect that is missing things, this is a huge =
breach to safety and reliability.

And you're right that other things can be checked, this is just about =
this single ability.
Like Laurenz says, that other checks could possibly be done is not a =
reason not to look or consider this.

Laurenz makes the right assumption about my message, and sorry to =
suggest an emphasis on a primary key
for doing the check. A primary key has a high chance of existing, and =
must contain all table rows pretty much
guaranteed, and therefore is a good candidate for validating the table, =
but this is really just a practical
way to be able to check if a table has all its segments available =
without requiring changes to the catalog.

My aim actually is to point out this, what I think, is an omission, =
where we are missing out.
I don't know what would fit best in the current engine, my suggestion is =
to keep track of segments, or the last
page that contains data and thus should be available, so that metadata =
is available to allow the database
to be able to validate if the main fork and all segments are available.


Frits Hoogland


> On 1 Oct 2025, at 08:26, Laurenz Albe <laurenz.albe@cybertec.at> =
wrote:
>=20
> On Tue, 2025-09-30 at 18:55 +0300, Aleksander Alekseev wrote:
>>> 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
>> I'm a bit puzzled by your emphasis on primary keys. In Postgres it is
>> legal to have tables without PKs, indexes, or even columns:
>>=20
>> =3D# create table my_table();
>> =3D# select * from my_table;
>>=20
>> To clarify, are you proposing not to check such tables?
>=20
> If there is no index on a table, there is no way to determine a =
missing
> segment.  I don't think that Frits deliberately wants to *not* check
> such tables, it's just that there is no way to do it as things are =
now.
>=20
> Just because a check is not possible in certain cases is no good =
reason
> to forgo a check in those cases where it is possible.
>=20
> Yours,
> Laurenz Albe


--Apple-Mail=_FC3CC28F-4B00-4A0C-8D17-AA21B2ECC544
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;">I am proposing the database to have the =
ability to detect when it has missing segments.<div>One of the main and =
basic properties of a database is to store data consistently and =
reliably.</div><div>If the database cannot detect that is missing =
things, this is a huge breach to safety and =
reliability.</div><div><br></div><div>And you're right that other things =
can be checked, this is just about this single ability.</div><div>Like =
Laurenz says, that other checks could possibly be done is not a reason =
not to look or consider this.</div><div><br></div><div>Laurenz makes the =
right assumption about my message, and sorry to suggest an emphasis on a =
primary key</div><div>for doing the check. A primary key has a high =
chance of existing, and must contain all table rows pretty =
much</div><div>guaranteed, and therefore is a good candidate for =
validating the table, but this is really just a practical</div><div>way =
to be able to check if a table has all its segments available without =
requiring changes to the catalog.</div><div><br></div><div>My aim =
actually is to point out this, what I think, is an omission, where we =
are missing out.</div><div>I don't know what would fit best in the =
current engine, my suggestion is to keep track of segments, or the =
last</div><div>page that contains data and thus should be available, so =
that metadata is available to allow the database</div><div>to be able to =
validate if the main fork and all segments are =
available.</div><div><br><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 08:26, Laurenz =
Albe &lt;laurenz.albe@cybertec.at&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>On Tue, 2025-09-30 at =
18:55 +0300, Aleksander Alekseev wrote:<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><br>I'm =
a bit puzzled by your emphasis on primary keys. In Postgres it =
is<br>legal to have tables without PKs, indexes, or even =
columns:<br><br>=3D# create table my_table();<br>=3D# select * from =
my_table;<br><br>To clarify, are you proposing not to check such =
tables?<br></blockquote><br>If there is no index on a table, there is no =
way to determine a missing<br>segment. &nbsp;I don't think that Frits =
deliberately wants to *not* check<br>such tables, it's just that there =
is no way to do it as things are now.<br><br>Just because a check is not =
possible in certain cases is no good reason<br>to forgo a check in those =
cases where it is possible.<br><br>Yours,<br>Laurenz =
Albe<br></div></div></blockquote></div><br></div></div></body></html>=

--Apple-Mail=_FC3CC28F-4B00-4A0C-8D17-AA21B2ECC544--