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

On Fri, Aug 5, 2022 at 9:18 AM 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
>
> PS: What happen in practice - attaching invalid index to head of huge (many
> TB) partitioned table.
>

Interesting test case... fwiw I was curious how one would get
themselves out of such a situation, and it doesn't look good. There is
no way to detach the attached index, and you can't drop just that
portion of the index.

pagila=# drop index test_part_1000000_id_key;
ERROR: cannot drop index test_part_1000000_id_key because index
test_id_key requires it
HINT: You can drop index test_id_key instead.

I also wondered if you had additional partitions, would adding a valid
index to a second partition, after reindexing the invalid index on the
first partition, force a re-evaluation of the parent and set it to
valid (since all parts are valid) but that also does not change the
parent index. This was a bit surprising to me and unfortunately afaict
this means the only way to fix this situation is to drop the parent
index (and any/all child indexes which might exist).

Still need to dig more to determine if there is a bug in the validity
checking code for the parent index or if the answer is that we should
disallow attaching invalid indexes altogether (this doesn't seem like
a large hurdle for users, but if we don't need to add it then lets
not).

Robert Treat
https://xzilla.net