Re: The ability of postgres to determine loss of files of the main fork

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(dot)albe(at)cybertec(dot)at> wrote:
>
> 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.
>>
>> 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:
>>
>> =# create table my_table();
>> =# select * from my_table;
>>
>> To clarify, are you proposing not to check such tables?
>
> 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.
>
> 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.
>
> Yours,
> Laurenz Albe