Re: BUG #18500: Detaching a partition with an index manually attached to the parent's index triggers Assert

On Sun, Jun 09, 2024 at 06:00:00AM +0000, PG Bug reporting form wrote:
> CREATE TABLE t (a integer) PARTITION BY RANGE (a);
> CREATE TABLE tp (a integer PRIMARY KEY);
> ALTER TABLE t ATTACH PARTITION tp FOR VALUES FROM (0) TO (1000);
>
> CREATE UNIQUE INDEX t_a_idx ON t (a);
> ALTER INDEX t_a_idx ATTACH PARTITION tp_pkey;
>
> \d+ t*
> ALTER TABLE t DETACH PARTITION tp;
>
> TRAP: failed Assert("constrForm->coninhcount == 0"), File:
> "pg_constraint.c", Line: 873, PID: 3272276
> ...
> #5  0x000055b26dc9b76f in ExceptionalCondition (
>     conditionName=conditionName@entry=0x55b26dd2d254
> "constrForm->coninhcount == 0",
>     fileName=fileName@entry=0x55b26dd2d230 "pg_constraint.c",
> lineNumber=lineNumber@entry=873) at assert.c:66

This DDL sequence completely breaks partitioned tree indexes AFAIU.
As reported, the inheritance count is wrong.  Anyway, from what I can
see, I think that the ATTACH PARTITION is wrong and it should issue an
error because this is attempting to attach an index used by a
constraint to a partitioned index which is *not* related to a
constraint in the parent.  I am pretty sure that more chirurgy around
AttachPartitionEnsureIndexes() with lookups at pg_constraint are
required here.

It's a bit disappointing that this would not work to make sure that
the index used by the partitioned table is on a primary key, ensuring
a consistent partition tree for the whole, with all indexes used in
the pk:
=# alter table t add primary key using index t_a_idx ;
ERROR:  0A000: ALTER TABLE / ADD CONSTRAINT USING INDEX is not
supported on partitioned tables
LOCATION:  ATExecAddIndexConstraint, tablecmds.c:9259

This sequence, however, is fine:
CREATE TABLE t (a integer) PARTITION BY RANGE (a);
CREATE TABLE tp (a integer PRIMARY KEY);
ALTER TABLE t ATTACH PARTITION tp FOR VALUES FROM (0) TO (1000);
alter table t add primary key (a);
ALTER TABLE t DETACH PARTITION tp;

Note that before the DETACH we have this index tree, with a consistent
coninhcount:
=# select * from pg_partition_tree('t_pkey');
  relid  | parentrelid | isleaf | level
---------+-------------+--------+-------
 t_pkey  | null        | f      |     0
 tp_pkey | t_pkey      | t      |     1
(2 rows)
--
Michael