MIME-Version: 1.0
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>
In-Reply-To: <68dcd1f2.df0a0220.3300c0.f7af@mx.google.com>
From: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
Date: Wed, 1 Oct 2025 13:08:33 +0300
Message-ID: 
 <CAE7r3MJBX9aBhfpqfjNYOPvY1KssEuXNGoOocuL4ixKK-TK+bw@mail.gmail.com>
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
To: Michael Banck <mbanck@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Aleksander Alekseev <aleksander@tigerdata.com>,
	Frits Hoogland <frits.hoogland@gmail.com>, pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAE7r3MJBX9aBhfpqfjNYOPvY1KssEuXNGoOocuL4ixKK-TK%2Bbw%40mail.gmail.com>
Precedence: bulk

Hi,

On Wed, Oct 1, 2025 at 10:02=E2=80=AFAM Michael Banck <mbanck@gmx.net> wrot=
e:
>
> 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@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.
> >
> > > ... 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@mbanck-lin-1:~$ psql -c "SELECT COUNT(*) FROM pgbench_accounts"
> |  count
> |----------
> | 20000000
> |(1 row)
> |
> |mbanck@mbanck-lin-1:~$ rm /tmp/pg_virtualenv.h8ObRG/data/18/regress/base=
/5/16462.1
> |mbanck@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@mbanck-lin-1:~$ psql -c "SELECT COUNT(*) FROM pgbench_accounts"
> |  count
> |---------
> | 7995392
> |(1 row)
> |
> |mbanck@mbanck-lin-1:~$ /usr/lib/postgresql/18/bin/pg_amcheck -v --heapal=
lindexed -t pgbench_accounts
> |pg_amcheck: including database "postgres"
> |pg_amcheck: in database "postgres": using amcheck version "1.5" in schem=
a "public"
> |pg_amcheck: checking heap table "postgres.public.pgbench_accounts"
> |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pkey"
> |mbanck@mbanck-lin-1:~$ echo $?
> |0
> |mbanck@mbanck-lin-1:~$ /usr/lib/postgresql/18/bin/pg_amcheck -v --heapal=
lindexed -i pgbench_accounts_pkey
> |pg_amcheck: including database "postgres"
> |pg_amcheck: in database "postgres": using amcheck version "1.5" in schem=
a "public"
> |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pkey"
> |mbanck@mbanck-lin-1:~$ echo $?
> |0
> |mbanck@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.


Best regards,
Arseniy Mukhin