Parent index on partitioned table could still be invalid even when children are not - Mailing list pgsql-bugs
| From | Dmytro Astapov |
|---|---|
| Subject | Parent index on partitioned table could still be invalid even when children are not |
| Date | |
| Msg-id | CAFQUnFj9SvgE_KB8NTmCBXDo2J7VsAVYoc0Uvtjx8fnLV6xRmg@mail.gmail.com Whole thread Raw |
| List | pgsql-bugs |
OS: Debian, Rock Linux
Postgres versions: 13.6, 15.6, 17.0
Setup:
create table tbl(id int, d date, payload text) partition by range(d);
create table tbl_2023 partition of tbl for values from ('2023-01-01') to ('2024-01-01');
create table tbl_2024 partition of tbl for values from ('2024-01-01') to ('2025-01-01');
create table tbl_2023 partition of tbl for values from ('2023-01-01') to ('2024-01-01');
create table tbl_2024 partition of tbl for values from ('2024-01-01') to ('2025-01-01');
-- Let's assume that the tbl is very large, as are the partitions,
-- so we are adding a new index by building it concurrently on partitions and then creating
-- it on the parent table, which should attach partition indexes
create unique index concurrently on tbl_2023(d,id);
create unique index concurrently on tbl_2024(d,id);
create unique index concurrently on tbl_2024(d,id);
-- Index on tbl_2024 ended up being invalid for whatever reason - in our specific case it was deadlock
-- I am going to simulate this by marking index as invalid in pg_index
update pg_index set indisvalid='f' where indexrelid ='tbl_2024_d_id_idx'::regclass;
-- We failed to notice that partition index is invalid, and built parent index anyway.
-- It was marked as invalid as well, so now we have two invalid indexes
create unique index on tbl(d,id);
select 'before', indexrelid::regclass from pg_index where not indisvalid ;
/* this outputs:
?column? indexrelid
before tbl_2024_d_id_idx
before tbl_d_id_idx
?column? indexrelid
before tbl_2024_d_id_idx
before tbl_d_id_idx
*/
-- We can fix the failed index on the partition with a simple reindex
reindex index concurrently tbl_2024_d_id_idx;
-- ... but this does not fix the index on parent, which is still invalid,
-- and there does not seem a way to make it valid
select 'after', indexrelid::regclass from pg_index where not indisvalid ;
-- and there does not seem a way to make it valid
select 'after', indexrelid::regclass from pg_index where not indisvalid ;
/* this outputs:
?column? indexrelid
after tbl_d_id_idx
after tbl_d_id_idx
*/
At this point, there seems to be no way to make the parent index be valid.
It either can't be reindexed (in v13), or reindex does not mark it as valid (v17). `reindex table tbl` does not mark it as valid either.
The only solution I was able to find is to detach all the partitions, drop the index (reindex does not work at this stage either), create an index again, and reattach all partitions:
alter table tbl detach partition tbl_2023;
alter table tbl detach partition tbl_2024;
drop index tbl_d_id_idx;
create unique index on tbl(d,id);
alter table tbl attach partition tbl_2023 for values from ('2023-01-01') to ('2024-01-01');
alter table tbl attach partition tbl_2024 for values from ('2024-01-01') to ('2025-01-01');
alter table tbl detach partition tbl_2024;
drop index tbl_d_id_idx;
create unique index on tbl(d,id);
alter table tbl attach partition tbl_2023 for values from ('2023-01-01') to ('2024-01-01');
alter table tbl attach partition tbl_2024 for values from ('2024-01-01') to ('2025-01-01');
Alternatively, one can also DROP INDEX tbl_d_id_idx, which would drop all index partitions, and start from scratch.
Here is db-fiddle for yor convenience if you want to play with it: https://www.db-fiddle.com/f/b67c8CfQECbTpk3RXkXnPr/0
Unfortunately, in my practical case the tables involved are very large, so detaching/reattaching partitions, or re-doing the index creation would lead to couple of days of extra work and runtime.
I think the code involved here is contained in the backend/commands/indexcmds.c
When the parent index is created, we arrive to this line in DefineIndex , where we would go over all indexes on partitions in search for a matching one.
If a match is found, it would be attached to the parent, which happens here. However, if it is invalid, then invalidate_parent is set to true, and this later triggers invalidation of the parent index a dozen of lines later, here. There does not seem to be a code path (outside of ALTER INDEX ... ATTACH PARTITION ...) that could mark the parent index valid again.
This feels like a bug, with the expected behaviour being "once the last invalid partition of an index becomes valid (and no partitions are missing), the partitioned index should become valid as well". Would you agree?
Also, is it naive to think that in this specific case marking the parent index as valid via pg_index update might be warranted as a solution?
--
Best regards, Dmytro
pgsql-bugs by date: