On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote: > We have been utilizing partitioned tables with indexes. We've recently had an > issue > where the parent table's index (id, date) became invalid (indisvalid=FALSE, > indisready=FALSE in pg_index). For reference the parent table is partitioned > on a > date field within the table.
Indexes don't become invalid just so. I wonder what happened. > We attempted to fix the issue by doing the following: > > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > DROP INDEX brokenchildindex; > CREATE INDEX newchildindex on table_badpartition using btree (id, date); > ALTER TABLE table_parent ATTACH PARTITION table_badpartition > FOR VALUES FROM (date) TO (date+1); > > This did not fix the issue so we attempted an alternate fix: > > begin; > set role readwrite; > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > ALTER TABLE table_badpartition RENAME TO table_badpartition_detached; > CREATE TABLE table_badpartition PARTITION OF table_parent > FOR VALUES FROM (date) TO (date+1); > ALTER TABLE table_badpartitioneplica identity full; > INSERT INTO table_badpartition (id, date, ...) > SELECT id, date, ... from table_badpartition_detached; > commit; > > This new table was created with the correct columns, the accurate data, and > the > correct indices however the parent index is still listed with indisvalid = > FALSE > and indisready = FALSE. You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index on the partitioned table into a valid index. Yours, Laurenz Albe
