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>
 <CAE7r3MJBX9aBhfpqfjNYOPvY1KssEuXNGoOocuL4ixKK-TK+bw@mail.gmail.com>
In-Reply-To: 
 <CAE7r3MJBX9aBhfpqfjNYOPvY1KssEuXNGoOocuL4ixKK-TK+bw@mail.gmail.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Thu, 9 Apr 2026 10:36:08 +0200
Message-ID: 
 <CAKZiRmw7TX05XNppAYj-aGiBCe6kJ2sWH+kPmju6hnKc0pFX8g@mail.gmail.com>
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
To: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
Cc: Michael Banck <mbanck@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	Aleksander Alekseev <aleksander@tigerdata.com>,
 Frits Hoogland <frits.hoogland@gmail.com>,
	pgsql-hackers@lists.postgresql.org,
	"Andrey M. Borodin" <x4mmm@yandex-team.ru>,
 Robert Haas <robertmhaas@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAKZiRmw7TX05XNppAYj-aGiBCe6kJ2sWH%2BkPmju6hnKc0pFX8g%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 1, 2025 at 12:08=E2=80=AFPM Arseniy Mukhin
<arseniy.mukhin.dev@gmail.com> wrote:
>
> Hi,
>
> On Wed, Oct 1, 2025 at 10:02=E2=80=AFAM Michael Banck <mbanck@gmx.net> wr=
ote:
> >
> > 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 t=
o
> > > >> verify_heapam() that causes the primary key index to be scanned an=
d 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 o=
f
> > > > the segments for a single table. And the *real* check will not diff=
er
> > > > 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/ba=
se/5/16462.1
> > |mbanck@mbanck-lin-1:~$ ls /tmp/pg_virtualenv.h8ObRG/data/18/regress/ba=
se/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 --heap=
allindexed -t pgbench_accounts
> > |pg_amcheck: including database "postgres"
> > |pg_amcheck: in database "postgres": using amcheck version "1.5" in sch=
ema "public"
> > |pg_amcheck: checking heap table "postgres.public.pgbench_accounts"
> > |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pke=
y"
> > |mbanck@mbanck-lin-1:~$ echo $?
> > |0
> > |mbanck@mbanck-lin-1:~$ /usr/lib/postgresql/18/bin/pg_amcheck -v --heap=
allindexed -i pgbench_accounts_pkey
> > |pg_amcheck: including database "postgres"
> > |pg_amcheck: in database "postgres": using amcheck version "1.5" in sch=
ema "public"
> > |pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pke=
y"
> > |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.

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-26CFC3E=
2CFAC%40yandex-team.ru
[1] - https://www.postgresql.org/message-id/CAKZiRmwCT%3DpqesAjC4-2rRLWQ2ui=
BkrmJBhWeTgALn6yfqbuew%40mail.gmail.com