Re: BUG #17997: Assert failed in validatePartitionedIndex() when attaching partition index to child of valid index - Mailing list pgsql-bugs

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