Re: Amcheck verification of GiST and GIN

On Thu, Feb 2, 2023 at 12:31 PM Nikolay Samokhvalov
<samokhvalov(at)gmail(dot)com> wrote:
> I understand your thoughts (I think) and agree with them, but at least one
> scenario where I do want to see *all* errors is corruption prevention – running
> amcheck in lower environments, not in production, to predict and prevent issues.
> For example, not long ago, Ubuntu 16.04 became EOL (in phases), and people
> needed to upgrade, with glibc version change. It was quite good to use amcheck
> on production clones (running on a new OS/glibc) to identify all indexes that
> need to be rebuilt. Being able to see only one of them would be very
> inconvenient. Rebuilding all indexes didn't seem a good idea in the case of
> large databases.

I agree that this matters at the level of whole indexes. That is, if
you want to check every index in the database, it is unhelpful if the
whole process stops just because one individual index has corruption.
Any extra information about the index that is corrupt may not be all
that valuable, but information about other indexes remains almost as
valuable.

I think that that problem should be solved at a higher level, in the
program that runs amcheck. Note that pg_amcheck will already do this
for B-Tree indexes. While verify_nbtree.c won't try to limp on with an
index that is known to be corrupt, pg_amcheck will continue with other
indexes.

We should add a "Tip" to the amcheck documentation on 14+ about this.
We should clearly advise users that they should probably just use
pg_amcheck. Using the SQL interface directly should now mostly be
something that only a tiny minority of experts need to do -- and even
the experts won't do it that way unless they have a good reason to.

--
Peter Geoghegan