Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix - Mailing list pgsql-general

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