| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | tablecmds: fix bug where index rebuild loses replica identity on partitions |
| Date: | 2026-01-27 05:13:32 |
| Message-ID: | DB533C25-C6BA-4C0F-8046-96168E9CDD72@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Hackers,
I found this bug while working on a related patch [1].
When ALTER TABLE ... ALTER COLUMN TYPE causes an index rebuild, and that index is used as REPLICA IDENTITY on a partitioned table, the replica
identity marking on partitions can be silently lost after the rebuild.
Below is a simple reproduction:
```
-- create a partitioned table and a parition
create table parent (id int not null, val int not null) partition by range (id);
create table child partition of parent for values from (1) to (100);
-- create an index on parent
create unique index indx_1 on parent(id, val);
-- the index is auto created on child, and both indexes’ indisreplident are false
select c.relname as index_name, c.oid as index_oid, i.indisreplident from pg_class c join pg_index i on c.oid = i.indexrelid where (c.relname = 'indx_1' or c.relname = 'child_id_val_idx’);
index_name | index_oid | indisreplident
------------------+-----------+----------------
indx_1 | 24594 | f
child_id_val_idx | 24595 | f
(2 rows)
-- as replica identity doesn’t recurse, set it on parent and child individually
alter table parent replica identity using index indx_1;
alter table child replica identity using index child_id_val_idx;
-- now both indexes are marked as replica identity
select c.relname as index_name, c.oid as index_oid, i.indisreplident from pg_class c join pg_index i on c.oid = i.indexrelid where (c.relname = 'indx_1' or c.relname = ‘child_id_val_idx');
index_name | index_oid | indisreplident
------------------+-----------+----------------
indx_1 | 24594 | t
child_id_val_idx | 24595 | t
(2 rows)
-- alter a column type, the column is part of the index, it will cause the index to rebuid
alter table parent alter val type bigint;
-- from the OIDs, we can see both indexes are rebuilt, but the child partition loses its replica identity marking
select c.relname as index_name, c.oid as index_oid, i.indisreplident from pg_class c join pg_index i on c.oid = i.indexrelid where (c.relname = 'indx_1' or c.relname = ‘child_id_val_idx');
index_name | index_oid | indisreplident
------------------+-----------+----------------
child_id_val_idx | 24597 | f
indx_1 | 24596 | t
(2 rows)
```
This patch fixes the bug by tracking replica identity indexes across partition hierarchies and restoring replica identity markings on all affected partitions after index rebuilds. Regression tests are added.
[1] https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-tablecmds-fix-bug-where-index-rebuild-loses-repli.patch | application/octet-stream | 19.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ashutosh Bapat | 2026-01-27 05:47:08 | Re: Import Statistics in postgres_fdw before resorting to sampling. |
| Previous Message | VASUKI M | 2026-01-27 05:11:09 | Re: Optional skipping of unchanged relations during ANALYZE? |