Thread: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
BUG #17574: Attaching an invalid index to partition head make head index invalid forever
From
PG Bug reporting form
Date:
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
Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
From
Maxim Boguk
Date:
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
From
Robert Treat
Date:
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
Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
From
Amit Langote
Date:
Hi, On Fri, Aug 5, 2022 at 10:18 PM 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 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