RE: Index (primary key) corrupt?

Internal

OK,

Took the mailing list out of the loop because some logging needed to be provided. Reincluding it now because the actual issue has been identified.
Putting the list back in so if anyone bumps into this one in the future, it might prove helpful. See Greg’s answer below (the relhasindex column of the pg_class dictionary table was indeed set to ‘false’ for that index, what caused this situation we will probably never find out).

Thanks to Greg and Adrian for staying on top of this one.

Cheers,

Wim.

From: Greg Sabino Mullane <htamfids(at)gmail(dot)com>
Sent: woensdag 18 maart 2026 17:42
To: Wim Rouquart <wim(dot)rouquart(at)kbc(dot)be>
Cc: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Subject: Re: Index (primary key) corrupt?

The real sender of this external email is htamfids(at)gmail(dot)com<mailto:htamfids(at)gmail(dot)com>

Ok, I can see the exact problem now in the logs. There is a section of pg_dump that tries to find the indexes, and it builds a list of oids to scan while doing so. In the logs you sent the unnest is an empty value '{}'. It should be '{1998823}'. Indeed, it shows up in other places properly, so there is something unique to that section of code. Looking deeper, that can fail to populated either because the table is "not interesting" (but it surely is by the other queries) or because it has no index. That in turn traces back to the relhasindex column of pg_class. My guess is that something has set that to 'f' when it should be 't'. Not the kind of corruption that can be detected by checksums or by anything like amcheck, as I'm pretty sure the index is there, but pg_class has been corrupted in a way to not allow pg_dump to find it.

(New email arrives). Yep - the post reindex one now has the oid in the unnest, which suggests relhasindex was flipped back.

Can you verify this theory with a: SELECT * FROM pg_class WHERE oid = 1998823;

See if relhasindex is true of false.

Is there any chance some tool/person has modified that field, perhaps as some sort of hack to temporarily disable indexes?

You can also run this global sanity check. It should return no rows:

select relname from pg_class c where relhasindex is false and exists (select 1 from pg_index where c.oid = indrelid);

That problem table can be fixed without a REINDEX by simply doing:

update pg_class set relhasinex=true where oid = 1998823;

Disclaimer <https://www.kbc.com/KBCmailDisclaimer>