pgsql-hackers
❮
tablecmds: fix bug where index rebuild loses replica identity on partitions
- Jump to comment-1Chao Li<li.evan.chao@gmail.com>Jan 27, 2026, 5:14 AM UTCHi 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:
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.-- 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)
[1] https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/- Jump to comment-1Michael Paquier<michael@paquier.xyz>Jan 27, 2026, 7:39 AM UTCOn Tue, Jan 27, 2026 at 01:13:32PM +0800, Chao Li wrote:
I found this bug while working on a related patch [1].
I am slightly confused by the tests included in the proposed patch.
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.
On HEAD, if I undo the proposed changes of tablecmds.c, the tests
pass. If I run the tests of the patch with the changes of
tablecmds.c, the tests also pass. These tests don't check what you
want them to.
- if (tab->replicaIdentityIndex)
+ if (tab->replicaIdentityIndexOids != NIL)
This looks wrong to me. This new list tracks the OIDs of indexeselog(ERROR, "relation %u has multiple indexes marked as replica identity", tab->relid);
where you'd want to make sure that relreplident is updated, and it
could be possible, based on your proposal, that multiple indexes are
stored in this list. The error message is at least not in line
anymore. Actually, do we really need this extra list at all? The
list of indexes to rebuild are tracked already in changedIndexOids,
and the partitioned indexes seem to be in it, no?
--
Michael- Jump to comment-1Chao Li<li.evan.chao@gmail.com>Jan 27, 2026, 7:59 AM UTC
On Jan 27, 2026, at 15:39, Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Jan 27, 2026 at 01:13:32PM +0800, Chao Li wrote: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.
Oops, that isn’t supposed to be so. I’ll check the test.
I am slightly confused by the tests included in the proposed patch.
On HEAD, if I undo the proposed changes of tablecmds.c, the tests
pass. If I run the tests of the patch with the changes of
tablecmds.c, the tests also pass.These tests don't check what you
want them to.- if (tab->replicaIdentityIndex)
+ if (tab->replicaIdentityIndexOids != NIL)
elog(ERROR, "relation %u has multiple indexes marked as replica identity", tab->relid);
No, changedIndexOids only tracks the root index OID. DefineIndex() will automatically rebuild indexes on all partitions, and won’t return the rebuilt index OIDs to “alter table”.
This looks wrong to me. This new list tracks the OIDs of indexes
where you'd want to make sure that relreplident is updated, and it
could be possible, based on your proposal, that multiple indexes are
stored in this list. The error message is at least not in line
anymore. Actually, do we really need this extra list at all? The
list of indexes to rebuild are tracked already in changedIndexOids,
and the partitioned indexes seem to be in it, no?
So, before index rebuild, this patch records potential affected indexes in replicaIdentityIndexOids, and after index rebuild, restore replica identity on them.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/- Jump to comment-1Chao Li<li.evan.chao@gmail.com>Jan 27, 2026, 8:30 AM UTC
On Jan 27, 2026, at 15:59, Chao Li <li.evan.chao@gmail.com> wrote:
On Jan 27, 2026, at 15:39, Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Jan 27, 2026 at 01:13:32PM +0800, Chao Li wrote: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.
I am slightly confused by the tests included in the proposed patch.
On HEAD, if I undo the proposed changes of tablecmds.c, the tests
pass. If I run the tests of the patch with the changes of
tablecmds.c, the tests also pass.
Okay, I see the problem is here:
Oops, that isn’t supposed to be so. I’ll check the test.
I missed to add column “val” into the index, so that alter type of val didn’t cause index rebuild.+CREATE UNIQUE INDEX test_replica_identity_partitioned_pkey ON test_replica_identity_partitioned (id);
Ideally, it’s better to also verify that index OIDs should have changed before and after alter column type, but I haven’t figured out how to do so. Do you have an idea?
Please see v2, the test should fail on master now.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/- Jump to comment-1Chao Li<li.evan.chao@gmail.com>Jan 28, 2026, 2:50 AM UTC
On Jan 27, 2026, at 16:30, Chao Li <li.evan.chao@gmail.com> wrote:
On Jan 27, 2026, at 15:59, Chao Li <li.evan.chao@gmail.com> wrote:
On Jan 27, 2026, at 15:39, Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Jan 27, 2026 at 01:13:32PM +0800, Chao Li wrote: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.
I am slightly confused by the tests included in the proposed patch.
On HEAD, if I undo the proposed changes of tablecmds.c, the tests
pass. If I run the tests of the patch with the changes of
tablecmds.c, the tests also pass.
Oops, that isn’t supposed to be so. I’ll check the test.
I just updated the test to store index OIDs before and after rebuild into 2 temp tables, so that we can compare the OIDs to verify rebuild happens and replica identity preserved.
Okay, I see the problem is here:
```
+CREATE UNIQUE INDEX testreplicaidentitypartitionedpkey ON testreplicaidentity_partitioned (id);
```
I missed to add column “val” into the index, so that alter type of val didn’t cause index rebuild.
Ideally, it’s better to also verify that index OIDs should have changed before and after alter column type, but I haven’t figured out how to do so. Do you have an idea?
I tried to port the test to master branch, and the test failed. From the test diff file, we can see replica identity lost on 3 leaf partitions:
With this patch, the test passes and all replica identity are preserved.@@ -360,9 +360,9 @@ ORDER BY b.index_name; index_name | rebuilt | ri_lost ---------------------------------------------------+---------+--------- - test_replica_identity_partitioned_p1_id_val_idx | t | f - test_replica_identity_partitioned_p2_1_id_val_idx | t | f - test_replica_identity_partitioned_p2_2_id_val_idx | t | f + test_replica_identity_partitioned_p1_id_val_idx | t | t + test_replica_identity_partitioned_p2_1_id_val_idx | t | t + test_replica_identity_partitioned_p2_2_id_val_idx | t | t test_replica_identity_partitioned_p2_id_val_idx | t | f test_replica_identity_partitioned_pkey | t | f (5 rows)
PFA v3:
* Enhanced the test.
* A small change in findpartitionreplicaidentityindexes(): if we will not update a partition, then unlock it.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/