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

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;

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.