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

27.06.2023 14:00, Alexander Lakhin wrote:
> 26.06.2023 11:05, Michael Paquier wrote:
>> A third solution that came into my mind just now would be to revisit
>> the choice done in AttachPartitionEnsureIndexes() where an invalid
>> index can be chosen as a match when creating the indexes on the
>> partitions, so as we are able to get to the bottom of a chain with
>> valid indexes for the whole tree.  I have been testing the attached
>> and it has been working here the way I'd expect when manipulating
>> partition trees with ATTACH PARTITION, though this breaks the scenario
>> of this bug report because we would now get a failure when attempting
>> to attach an index in the last command.
>
> Thanks for the fix!
>
> This solution seems sensible to me. The only downside I see is that an
> invalid index would be left orphaned after ATTACH PARTITION, but I couldn't
> find in doc/ or src/test/regress/ any promises that such index must be
> used. I also don't see a way to make a previously valid index inside the
> partition index tree invalid and available to attaching a child index to it
> in the same time.

There is also another scenario where the new behavior could be considered as more sensible:
create table t(a int, b int) partition by range (a);
create index on t((a / b));
create table tp1(a int, b int);
insert into tp1 values (1, 0);
create index concurrently on tp1((a/b)); --  division by zero occurs, but the index is created (as invalid)
alter table t attach partition tp1 for values from (1) to (10);

Without the fix you get partition tp1_1 attached and the following partition indexes:
Partitioned index "public.t_expr_idx"
btree, for table "public.t"
Partitions: tp1_expr_idx

Index "public.tp1_expr_idx"
btree, for table "public.tp1", invalid

But with the patch applied ATTACH PARTITION fails with ERROR: division by zero.

Though we still can get a partition index chain with invalid indexes as follows:
create table t(a int, b int) partition by range (a);
create table tp1(a int, b int) partition by range (a);
alter table t attach partition tp1 for values from (1) to (100);
create table tp1_1(a int, b int);
insert into tp1_1 values (1, 0);
create index concurrently on tp1_1((a/b)); --  division by zero occurs, but the index is created (as invalid)
alter table tp1 attach partition tp1_1 for values from (1) to (10);
create index on t((a / b));

here we get the following index chain:
Partitioned index "public.t_expr_idx"
btree, for table "public.t"
Partitions: tp1_expr_idx, PARTITIONED

Partitioned index "public.tp1_expr_idx"
btree, for table "public.tp1", invalid
Partitions: tp1_1_expr_idx

Index "public.tp1_1_expr_idx"
btree, for table "public.tp1_1", invalid

It's also interesting that REINDEX for the index tree validates only a leaf index:
reindex index t_expr_idx;
ERROR:  division by zero

update tp1_1 set b=1;
reindex index t_expr_idx; -- or even reindex index tp1_expr_idx;

Partitioned index "public.t_expr_idx"
btree, for table "public.t"
Partitions: tp1_expr_idx, PARTITIONED

Partitioned index "public.tp1_expr_idx"
btree, for table "public.tp1", invalid
Partitions: tp1_1_expr_idx

Index "public.tp1_1_expr_idx"
btree, for table "public.tp1_1"

Although it looks like the invalid mark for a non-leaf index doesn't prevent using an index below it:
set enable_seqscan = off;
explain select * from t where a / b = 1;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Index Scan using tp1_1_expr_idx on tp1_1 t  (cost=0.12..8.14 rows=1 width=8)
    Index Cond: ((a / b) = 1)

So it's not clear (to me, at least), what exactly indisvalid means for indexes in a partition tree.

Best regards,
Alexander