Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
Maxim Boguk <maxim.boguk@gmail.com>
From: Maxim Boguk <maxim.boguk@gmail.com>
To: Robert Treat <rob@xzilla.net>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2022-08-09T23:27:49Z
Lists: pgsql-bugs
On Wed, Aug 10, 2022 at 4:51 AM Robert Treat <rob@xzilla.net> wrote: > On Fri, Aug 5, 2022 at 9:18 AM PG Bug reporting form > <noreply@postgresql.org> wrote: > > > > 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. > > > > 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 "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"