Re: BUG #17997: Assert failed in validatePartitionedIndex() when attaching partition index to child of valid index

On Wed, Jun 28, 2023 at 04:02:15PM +0900, Michael Paquier wrote:
> Exotic case, leading to this tree:
> =# select indisvalid, relid, parentrelid, isleaf, level
> from pg_partition_tree('t_expr_idx'), pg_index where indexrelid = relid;
> indisvalid | relid | parentrelid | isleaf | level
> ------------+----------------+--------------+--------+-------
> t | t_expr_idx | null | f | 0
> f | tp1_expr_idx | t_expr_idx | f | 1
> f | tp1_1_expr_idx | tp1_expr_idx | t | 2
> (3 rows)
>
> This looks like a second bug to me in DefineIndex() where we shouldn't
> even try to link to the invalid index tp1_1_expr_idx, and we should
> try to create a new index instead. Repeating the last CREATE INDEX
> command leads to a failure, which is what I'd expect, because we get
> the computation failure.

I have looked into this one, and decided that we should just rely on
the existing business in DefineIndex() where we switch indisvalid to
false on a parent if the index found for the match is itself invalid.
The code had two bugs:
- First, we did not re-check if an index freshly created was itself
invalid, which could happen when recursing through more than one
level for the creation of a partitioned index.
- We need to have a CCI after switch the parent's indisvalid to false,
so as the follow up recursions are able to see that, and do the update
of indisvalid across the parents up to the top-most parent.

Attached is a patch to fix all that, with regression tests that play
with multiple partition levels to show the recursion and the problems
we'd have without the patch.

Alexander, what do you think?
--
Michael