BUG #17574: Attaching an invalid index to partition head make head index invalid forever - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17574
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 14.4
Operating system:   Linux
Description:

If you (operator error or script error) attach an invalid index to head of
partition index - it will make partition head index invalid forver.
I found no way to fix situation except create completely new head index and
build/attach new indexes on all partitions.

Minimal test case:
--prepare data
create table test (id integer) partition by range(id);
create table test_part_1000000 partition of test for values from (0) to
(1000000);
insert into test_part_1000000 select (random()*999999)::integer from
generate_series(1, 10000000);
create index test_id_key on only test(id);

--so far ok index invalid
\d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--make an invalid index on partitiomn
create index CONCURRENTLY test_part_1000000_id_key on
test_part_1000000(id);
^CCancel request sent
ERROR:  canceling statement due to user request

--attach an invalid index ??? ok/not ok? is it should be allowed at all?
alter index test_id_key attach partition test_part_1000000_id_key;

--test_id_key invalid (expected)
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try to fix
reindex index CONCURRENTLY test_id_key;

--no effect still invalid
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try to fix even more seriously
reindex index test_id_key;

--still invalid
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID
Partitions: test_part_1000000 FOR VALUES FROM (0) TO (1000000)

--try other way around
reindex index test_part_1000000_id_key;
--and again invalid on head
postgres=# \d+ test
                                    Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression |
Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |
             | 
Partition key: RANGE (id)
Indexes:
    "test_id_key" btree (id) INVALID

PS: What happen in practice - attaching invalid index to head of huge (many
TB) partitioned table.

Regards,
Maxim