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 (
