Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix

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.

In order to find the indices causing the problem we utilized the following:

with invalid_indices as (
select    n.nspname,    c.relname as parent_index_name,    i.indrelid parent_table_oid,    i.indexrelid parent_index_oid,    x.indexdef as parent_indexdef,    substring(pg_get_indexdef(i.indexrelid), '.* btree \((.*)\)') as parent_index_cols
from    pg_catalog.pg_class c, pg_catalog.pg_namespace n,    pg_catalog.pg_index i, pg_catalog.pg_indexes x
where true    and (i.indisvalid = false or i.indisready = false)    and i.indexrelid = c.oid and c.relnamespace = n.oid    and n.nspname != 'pg_catalog'    and n.nspname != 'information_schema'    and n.nspname != 'pg_toast'    and n.nspname = x.schemaname    and c.relname = x.indexname
),
tables_with_invalid_indices as (    select    i.*, c.relname as parent_table_name    from invalid_indices i    left join pg_class c    on i.parent_table_oid = c.oid
),
children_of_tables_with_invalid_indices as (    select    t.*,    i.inhrelid as child_table_oid,    c.relname as child_table_name    from tables_with_invalid_indices t    left join pg_inherits i    on t.parent_table_oid = i.inhparent    left join pg_class c    on i.inhrelid = c.oid
),
-- for each index on parent table, left join against index on child table
all_indices_on_children_of_tables_with_invalid_indices as
(    select    c.*,    a.oid as child_index_oid,    a.relname as child_index_name,    a.relispartition as child_index_ispartition,    h.inhparent as parent_of_child_index_oid,    x.indexdef as child_indexdef,    substring(pg_get_indexdef(a.oid), '.* btree \((.*)\)') as child_index_cols    from children_of_tables_with_invalid_indices c    left join pg_index i    on c.child_table_oid = i.indrelid    inner join pg_class a    on i.indexrelid = a.oid    and parent_index_cols = substring(pg_get_indexdef(a.oid), '.* btree \((.*)\)')    left join pg_indexes x    on a.relname = x.indexname    left join pg_inherits h    on h.inhrelid = a.oid
),
unattached_indices_on_child_tables as
(    select    *    from all_indices_on_children_of_tables_with_invalid_indices    where not child_index_ispartition
),
missing_indices_on_child_tables as
(
select
a.*,
b.child_index_oid,
b.child_index_name,
b.child_index_ispartition,
b.child_indexdef,
b.parent_of_child_index_oid
from children_of_tables_with_invalid_indices a
left join all_indices_on_children_of_tables_with_invalid_indices b
on a.child_table_name = b.child_table_name
and a.parent_index_oid = b.parent_of_child_index_oid
where b.parent_of_child_index_oid is null
),
-- select * from all_indices_on_children_of_tables_with_invalid_indices
problems as (
select   u.parent_table_name,   u.parent_index_name,   u.child_table_name,   u.child_index_name,   u.parent_indexdef,   concat_ws(' ','ALTER INDEX', u.parent_index_name, 'ATTACH PARTITION', u.child_index_name, ';') as fix_sql,   u.child_index_cols,   u.parent_index_cols

from unattached_indices_on_child_tables u
union
select    m.parent_table_name,    m.parent_index_name,    m.child_table_name,    m.child_index_name,    m.parent_indexdef,    'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || 'ix_ledger_' || m.child_table_name || '_' || replace(m.parent_index_cols, ', ', '_') || ' ON ' || m.child_table_name || ' USING btree (' || m.parent_index_cols || ');' as fix_sql,    '' as child_index_cols,    m.parent_index_cols
from    missing_indices_on_child_tables m
)
select * from problems;

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.

We did some research within the mailing list archives and found a mention that this was an issue back in 2018 (https://postgrespro.com/list/thread-id/2416574) with a discussion in pghackers (https://www.postgresql.org/message-id/20181203225019.2vvdef2ybnkxt364@alvherre.pgsql) mentioning a patch.

Is this still a known issue? Or is there a way that we haven't thought of to fix the invalid parent index without reindexing?

Thanks,

Noel Parker

she/they