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

On Wed, Oct 1, 2025 at 12:08 PM Arseniy Mukhin
<arseniy(dot)mukhin(dot)dev(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Wed, Oct 1, 2025 at 10:02 AM Michael Banck <mbanck(at)gmx(dot)net> wrote:
> >
> > Hi,
> >
> > wow, this is one of the most terrifying threads I've ever seen...
> >
> > On Tue, Sep 30, 2025 at 12:41:29PM -0400, Tom Lane wrote:
> > > Aleksander Alekseev <aleksander(at)tigerdata(dot)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?
> >
> > > However, I think the requested functionality already exists via
> > > contrib/amcheck (see the heapallindexed option).
> >
> > It doesn't balk for me, am I doing something wrong?
> >
> > |mbanck(at)mbanck-lin-1:~$ psql -c "SELECT COUNT(*) FROM pgbench_accounts"
> > | count
> > |----------
> > | 20000000
> > |(1 row)
> > |
> > |mbanck(at)mbanck-lin-1:~$ rm /tmp/pg_virtualenv.h8ObRG/data/18/regress/base/5/16462.1
> > |mbanck(at)mbanck-lin-1:~$ ls /tmp/pg_virtualenv.h8ObRG/data/18/regress/base/5/16462* | grep -v 16462_
> > |/tmp/pg_virtualenv.h8ObRG/data/18/regress/base/5/16462
> > |/tmp/pg_virtualenv.h8ObRG/data/18/regress/base/5/16462.2
> > |mbanck(at)mbanck-lin-1:~$ psql -c "SELECT COUNT(*) FROM pgbench_accounts"
> > | count
> > |---------
> > | 7995392
> > |(1 row)
> > |
> > |mbanck(at)mbanck-lin-1:~$ /usr/lib/postgresql/18/bin/pg_amcheck -v --heapallindexed -t pgbench_accounts
> > |pg_amcheck: including database "postgres"
> > |pg_amcheck: in database "postgres": using amcheck version "1.5" in schema "public"
> > |pg_amcheck: checking heap table "postgres.public.pgbench_accounts"
> > |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pkey"
> > |mbanck(at)mbanck-lin-1:~$ echo $?
> > |0
> > |mbanck(at)mbanck-lin-1:~$ /usr/lib/postgresql/18/bin/pg_amcheck -v --heapallindexed -i pgbench_accounts_pkey
> > |pg_amcheck: including database "postgres"
> > |pg_amcheck: in database "postgres": using amcheck version "1.5" in schema "public"
> > |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pkey"
> > |mbanck(at)mbanck-lin-1:~$ echo $?
> > |0
> > |mbanck(at)mbanck-lin-1:~$
>
> I tried to repeat it and has the same results with bt_index_check().
> IIUC the reason amcheck doesn't show any corruption here is that
> allheapindexed check just builds bloom filter for all index tuples and
> then test every heap tuple against it. So we actually never check that
> every index tuple points to the existing segment/page/heap_tuple here.

So for the sake of archives and interested people, if one wants to discover
such conditions (using amcheck), please take a look on nearby more recent
thread [0] where Andrey attempts to add bloom build functionality for heap
(index-all-keys-match) and specifically [1] where I try to test&enhance
It seems to me that checking for checking for itup's heap block pointing
outside current heap max size seems enough to discover such condition
reported here (but of course Andrey's attempt is much more advanced than
just that).

-J.

[0] - https://www.postgresql.org/message-id/432626F9-65DF-4F0D-B345-26CFC3E2CFAC%40yandex-team.ru
[1] - https://www.postgresql.org/message-id/CAKZiRmwCT%3DpqesAjC4-2rRLWQ2uiBkrmJBhWeTgALn6yfqbuew%40mail.gmail.com