Message-ID: <68dcd1f2.df0a0220.3300c0.f7af@mx.google.com>
Sender: Michael Banck <mbanck@gmail.com>
Date: Wed, 1 Oct 2025 09:02:09 +0200
From: Michael Banck <mbanck@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Aleksander Alekseev <aleksander@tigerdata.com>,
	Frits Hoogland <frits.hoogland@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
References: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com>
 <CAJ7c6TMYvYBQpgv8k+4QnajqPcroOzrj7d3snBBTKX4tHQfVYw@mail.gmail.com>
 <499686.1759250489@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <499686.1759250489@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/68dcd1f2.df0a0220.3300c0.f7af%40mx.google.com>
Precedence: bulk

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