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

On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote:
> 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.

Indexes don't become invalid just so. I wonder what happened.

> 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.

You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index on the
partitioned table into a valid index.

Yours,
Laurenz Albe