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

Thank you for your response. I forgot to include it but we did check for
that prior to our submission. We used this query to see how many indexes
were related to the parent index via the inherits table:

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.

On Thu, Dec 7, 2023 at 10:26 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

>
> This is an EXTERNAL EMAIL. STOP. THINK before you CLICK links or OPEN
> attachments.
>
> ______________________________________________________________________
> 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
>

--
*Noel Parker*
Data Engineer
Pronouns: She/Her, They/Them
*apexfintechsolutions.com <http://apexfintechsolutions.com>*