Treat invalid indexes as absent in schema validation
What does this MR do and why?
The schema validation treats invalid indexes as absent. Before, it read every index from pg_indexes, including indexes that PostgreSQL marks as invalid, for example after a failed concurrent index build:
- An invalid index under the expected name let
gitlab:db:validate_schemapass, although the index does not work and an invalid unique index does not enforce uniqueness. It is now reported as missing. - Invalid leftovers of reindexing (for example
*_ccnew) were reported as extra. They are not reported now.
A partitioned index is still read when it is invalid. PostgreSQL marks a partitioned index invalid while a partition has no child index, and that child is reported as missing instead of the parent.
This changes the pre-upgrade check: an instance that has an invalid index under an expected name now fails gitlab:db:validate_schema.
A new monolith spec, spec/lib/gitlab/schema/validation/sources/database_spec.rb, runs the index query against the test database. The gem specs stub the connection and the gem CI has no database, so they cannot test the filter.
Split out of !258353 (merged). Related to #632819.
How to validate locally
On a throwaway PostgreSQL 17 database, create a partitioned table with two partitions, an ON ONLY parent index, and a child index attached for the first partition only. Run MissingIndexes with a structure.sql that has both children: it reports only the missing child, not the invalid parent. Then mark a normal index invalid with UPDATE pg_index SET indisvalid = false: master does not report it, this branch reports it as missing.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.