So having throught about this a bit more, and having had some real-world experience with the script now, I have an idea that might work and some questions to make it succeed.
My thinking is to add a new form of vacuum called VACUUM FSCK.
This would:
1. lock pg_class in exclusive mode (or do I need exclusive access?), as this is needed to solve the race conditions. As I see, this seems to bring the database to a screeching halt concurrency-wise (but unlike my script would allow other databases to be accessed normally).
2. read the files where the name consists of only digits out of the filesystem and compare with oids from pg_class and relfilenodes
3. Any file not found in that list would then unlink it, as well as any files with the patter followed by an underscore or period.
This would mean that the following cases would not be handled:
If you have the first extent gone but later extents are present we check on the first extant, and so would not see the later ones. Same goes for visibility maps and other helper files.
If you add a file in the directory which has a name like 34F3A222BC, that would never get cleaned up because it contains non-digits.
So this leads to the following questions:
1. Is locking pg_class enough to avoid race conditions? Is exclusive mode sufficient or do I need exclusive access mode?
2. would it be preferable to move the file to a directory rather than unlinking it?
3. Should I perform any sort of check on the tables at the end to make sure everything is ok?
