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

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 --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@mbanck-lin-1:~$ echo $?
|0
|mbanck@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@mbanck-lin-1:~$ echo $?
|0
|mbanck@mbanck-lin-1:~$

And neither pg_checksums nor pg_basebackup catch it either...


Michael