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

Hello all,

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