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

On Sun, Jun 25, 2023 at 10:40:58AM -0400, Tom Lane wrote:
> Thanks for the report!  This is not new in 16, it reproduces at
> least as far back as v11.  I suppose we can't simply remove the
> assertion, but have to figure out what should happen and write
> code for that.

The cause of the crash is the index created by the second command on
the partitioned table "t", which creates the index with indisvalid set
to true because it has no partitions yet.

The crash then happens in the last command because we attach the index
of tp1_1, and the index validation is a two-step process by going
through the partition tree up to the parent:
- First it goes to the index of tp1, which has indisvalid = false.
- Then it goes up to the index of tp, which has indisvalid = true,
failing on the assertion.

71a05b22 has made the creation of invalid indexes for partitioned
tables much softer, still I think that it is correct to mark an index
as valid even if there are no partitions yet.

It seems to me that the mistake in this sequence is when we attach tp1
to t, in the 6th command of what has been reported when attaching a
partition?  I mean this one:
alter table t attach partition tp1 for values from (1) to (100);

At this stage, tp1_1 is attached already to tp1, making the partition
tree becoming t -> tp1 -> tp1_1.  The index attached to tp1 is marked
as invalid, because tp1_1 does not have an index.

It seems to me that there are two potential solutions here:
1) We could recurse and make sure that the index of t *also* becomes
*invalid*, because t_a_idx and tp1_a_idx form a partition chain, still
the leaf tp1_1 does *not* have an index yet, making it unusable.
2) Then I found about AttachPartitionEnsureIndexes().  This is called
when attaching a partition to make sure that indexes are created to
match the indexes between the partitioned table and the new partition
attached to it, but it does not recurse when attaching a partitioned
table, or an index would have been created on tp1_1 when running the
6th command to complete the index tree all the way down.

At the end, I'd like to think that 2) is the right path forward.  For
example, this creates a partitioned index automatically when attaching
a partition:
create table t(a int) partition by range (a);
create index on t(a);
create table tp1(a int) partition by range (a);
alter table t attach partition tp1 for values from (1) to (100);
=# \di
                    List of relations
 Schema |   Name    |       Type        | Owner  | Table
--------+-----------+-------------------+--------+-------
 public | t_a_idx   | partitioned index | popopo | t
 public | tp1_a_idx | partitioned index | popopo | tp1
(2 rows)
--
Michael