Thank you for your response. I forgot to include it but we did check for that prior to our submission. We used this query to see how many indexes were related to the parent index via the inherits table:
SELECT count(inh.inhrelid) FROM pg_class c inner join pg_inherits inh on c.oid = inh.inhparent WHERE c.oid = broken_parent_index_oid This query returned a value equal to the number of partitions which implies that all of the indexes on the partitions are attached to the parent index that is showing up as broken. As an additional check this morning I also confirmed that the indexes were of the same definition and that one of the others (date, not_id) did not somehow get attached to this parent index using this: SELECT par.relname parent_index, chi.relname child_index, substring(pg_get_indexdef(par.oid), '.* btree \((.*)\)') as parent_index_cols, substring(pg_get_indexdef(chi.oid), '.* btree \((.*)\)') as child_index_cols, par.oid parent_index_oid, inh.inhparent inherit_parent_oid, inh.inhrelid inh_child_oid, ind.indexrelid child_index_oid FROM pg_inherits inh join pg_index ind on inh.inhrelid = ind.indexrelid join pg_class chi on ind.indexrelid = chi.oid join pg_class par on inh.inhparent = par.oid WHERE inh.inhparent = broken_parent_index_oid; >From this I'm seeing that the columns in the definitions are matching between the two in addition to being attached. On Thu, Dec 7, 2023 at 10:26 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > This is an EXTERNAL EMAIL. STOP. THINK before you CLICK links or OPEN > attachments. > > ______________________________________________________________________ > 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 > -- *Noel Parker* Data Engineer Pronouns: She/Her, They/Them *apexfintechsolutions.com <http://apexfintechsolutions.com>*
