tablecmds: fix bug where index rebuild loses replica identity on partitions

  • Jump to comment-1
    Chao Li<li.evan.chao@gmail.com>
    Jan 27, 2026, 5:14 AM UTC
    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/
    • Jump to comment-1
      Michael Paquier<michael@paquier.xyz>
      Jan 27, 2026, 7:39 AM UTC
      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. 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);
      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?
      --
      Michael
      • Jump to comment-1
        Chao 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.

        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.
        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);

        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?
        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”.
        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-1
          Chao 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.

          Oops, that isn’t supposed to be so. I’ll check the test.
          Okay, I see the problem is here:
          +CREATE UNIQUE INDEX test_replica_identity_partitioned_pkey ON test_replica_identity_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?
          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-1
            Chao 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.

            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 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.
            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:
            @@ -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)
            With this patch, the test passes and all replica identity are preserved.
            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/