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

On Wed, Aug 10, 2022 at 4:51 AM Robert Treat <rob(at)xzilla(dot)net> wrote:

> 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
>

I explored a lot different ways to fix issue (including ones you
suggested), nothing help, so I ended with creating whole new index and drop
old invalid index as only way out of issue.
Probably just catalog update set indisvalid='true' on head idex might be
sufficient but I wasn't ready for such experiments on the 15TB
mission-critical database.

--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"