Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever

Hi,

On Fri, Aug 5, 2022 at 10:18 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 17574
> Logged by: Maxim Boguk
> Email address: maxim(dot)boguk(at)gmail(dot)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

ISTM that the REINDEX code never looks at the indexes belonging to a
parent partitioned table, which are just catalog entries, and only
ever processes the partitions' copies of those indexes. Perhaps, it
makes sense for REINDEX to at least update the indisvalid flag on a
parent's index using validatePartitionedIndex(), as
ATExecAttachPartitionIdx() does.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com