Re: Checking for missing heap/index files

> On Jun 8, 2022, at 5:45 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> Is this something anyone has even needed or had requested?

I might have put this in amcheck's verify_heapam() had there been an interface for it. I vaguely recall wanting something like this, yes.

As it stands, verify_heapam() may trigger mdread()'s "could not open file" or "could not read block" error, in the course of verifying the table. There isn't an option in amcheck to just verify that the underlying files exist. If struct f_smgr had a function for validating that all segment files exist, I may have added an option to amcheck (and the pg_amcheck frontend tool) to quickly look for missing files.

Looking at smgr/md.c, it seems mdnblocks() is close to what we want, but it skips already opened segments "to avoid redundant seeks". Perhaps we'd want to add a function to f_smgr, say "smgr_allexist", to check for all segment files? I'm not sure how heavy-handed the corresponding mdallexist() function should be. Should it close all open segments, then reopen and check the size of all of them by calling mdnblocks()? That seems safer than merely asking the filesystem if the file exists without verifying that it can be opened.

If we made these changes, and added corresponding quick check options to amcheck and pg_amcheck, would that meet your current needs? The downside to using amcheck for this sort of thing is that we did not (and likely will not) back port it. I have had several occasions to want this functionality recently, but the customers were on pre-v14 servers, so these tools were not available anyway.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company