Proposal: Cascade REPLICA IDENTITY changes to leaf partitions
Hi,
While working with logical replication and partitioned tables, I noticed an
inconsistency between how publications treat partitions and how "ALTER
TABLE ... REPLICA IDENTITY" behaves.
When a publication is created on a partitioned table, e.g.:
```
CREATE PUBLICATION pub FOR TABLE parent;
```
PostgreSQL automatically includes all leaf partitions of the table in the
publication. This matches the user’s expectation that a partitioned table
behaves as a single logical entity.
However, if the user then runs:
```
ALTER TABLE parent REPLICA IDENTITY FULL;
```
only the parent table’s relreplident is updated. None of the leaf
partitions inherit this change, even though the parent itself has no
storage and its replication identity plays no role in logical replication.
Logical decoding always operates on the leaf partitions, and their
replication identities determine whether UPDATE/DELETE can be replicated
safely.
This gap leads to several problems:
* The parent table’s replica identity is effectively irrelevant during
logical replication, since it never stores tuples or produces WAL.
* Users expect that altering the replica identity on the partitioned table
would apply to all partitions that are implicitly included in the
publication.
* As a result, users currently need to run ALTER TABLE ... REPLICA
IDENTITY separately for every leaf partition, which is tedious and
error-prone on large partition hierarchies.
* Misconfiguration usually surfaces only when logical replication starts
failing on UPDATE/DELETE for specific leaf partitions due to mismatched
replica identities.
To address this, the attached patch makes:
```
ALTER TABLE parent REPLICA IDENTITY <type>
```
cascade the new setting to all leaf partitions of the table. Partitioned
tables (RELKIND_PARTITIONED_TABLE) are skipped since they have no storage
and no effective replica identity.
This aligns ALTER TABLE behavior with how publications already expand
partitioned tables, and makes replication identity configuration consistent
with logical replication semantics.
The attached patch is not yet fully ready for detailed review, this is more
of a proof-of-concept. At this stage, I mainly want to see whether people
agree with the idea, or if there are objections to cascading replica
identity changes for partitioned tables before I refine the patch further.
Comments and feedback are welcome.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
v1-0001-tablecmds-Cascade-REPLICA-IDENTITY-changes-to-lea.patchapplication/octet-stream; name=v1-0001-tablecmds-Cascade-REPLICA-IDENTITY-changes-to-lea.patchDownload
From ef094e7eba68c0d97ba1f0b46a61db376f4e8a56 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Thu, 11 Dec 2025 17:03:37 +0800
Subject: [PATCH v1] tablecmds: Cascade REPLICA IDENTITY changes to leaf
partitions
ALTER TABLE ... REPLICA IDENTITY currently updates only the
partitioned table itself, even though partitioned tables have no
storage and their replica identity is never used by logical
replication. The replica identity that matters for replication is the
setting on each leaf partition, but users must alter those tables
individually today.
Extend ATExecReplicaIdentity() so that when invoked on a partitioned
table, the requested replica identity is applied automatically to all
leaf partitions. This makes replica identity configuration consistent
with how publications automatically include leaf partitions, and avoids
silent misconfigurations where only some partitions can safely
replicate UPDATE or DELETE operations.
Author: Chao Li <lic@highgo.com>
---
src/backend/commands/tablecmds.c | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1c9ef53be20..ba4f3331870 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18482,6 +18482,25 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
Oid indexOid;
Relation indexRel;
int key;
+ List *partRelIds = NIL;
+
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /* Also apply to partitions, if any */
+ partRelIds = GetPubPartitionOptionRelations(partRelIds, PUBLICATION_PART_LEAF,
+ RelationGetRelid(rel));
+ if (list_length(partRelIds) > 0)
+ {
+ foreach_oid(partRelOid, partRelIds)
+ {
+ Relation partRel;
+
+ partRel = relation_open(partRelOid, lockmode);
+ ATExecReplicaIdentity(partRel, stmt, lockmode);
+ relation_close(partRel, lockmode);
+ }
+ }
+ }
if (stmt->identity_type == REPLICA_IDENTITY_DEFAULT)
{
--
2.39.5 (Apple Git-154)
On Thu, Dec 11, 2025 at 2:46 PM Chao Li <li.evan.chao@gmail.com> wrote:
Hi,
While working with logical replication and partitioned tables, I noticed an inconsistency between how publications treat partitions and how "ALTER TABLE ... REPLICA IDENTITY" behaves.When a publication is created on a partitioned table, e.g.:
```
CREATE PUBLICATION pub FOR TABLE parent;
```PostgreSQL automatically includes all leaf partitions of the table in the publication. This matches the user’s expectation that a partitioned table behaves as a single logical entity.
However, if the user then runs:
```
ALTER TABLE parent REPLICA IDENTITY FULL;
```
only the parent table’s relreplident is updated. None of the leaf partitions inherit this change, even though the parent itself has no storage and its replication identity plays no role in logical replication. Logical decoding always operates on the leaf partitions, and their replication identities determine whether UPDATE/DELETE can be replicated safely.This gap leads to several problems:
* The parent table’s replica identity is effectively irrelevant during logical replication, since it never stores tuples or produces WAL.
When we use row filters, if publish_via_partition_root option of
publication is true, the root partitioned table's row filter is used.
I think this would then refer RI of partitioned table for validity of
row filter. Please see docs [1]https://www.postgresql.org/docs/devel/sql-createpublication.html (There can be a case where a
subscription combines multiple publications. If a partitioned table is
published by any subscribed publications which set
publish_via_partition_root = true, changes on this partitioned table
(or on its partitions) will be published using the identity and schema
of this partitioned table rather than that of the individual
partitions. This parameter also affects how row filters and column
lists are chosen for partitions; see below for details.) for more
details.
I have not tested it but you can once try to see how it behaves.
The other point is what if one of the partition already has RI defined
to a different value than what is defined for parent table?
[1]: https://www.postgresql.org/docs/devel/sql-createpublication.html
--
With Regards,
Amit Kapila.
On Dec 11, 2025, at 20:43, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Thu, Dec 11, 2025 at 2:46 PM Chao Li <li.evan.chao@gmail.com> wrote:
Hi,
While working with logical replication and partitioned tables, I noticed an inconsistency between how publications treat partitions and how "ALTER TABLE ... REPLICA IDENTITY" behaves.When a publication is created on a partitioned table, e.g.:
```
CREATE PUBLICATION pub FOR TABLE parent;
```PostgreSQL automatically includes all leaf partitions of the table in the publication. This matches the user’s expectation that a partitioned table behaves as a single logical entity.
However, if the user then runs:
```
ALTER TABLE parent REPLICA IDENTITY FULL;
```
only the parent table’s relreplident is updated. None of the leaf partitions inherit this change, even though the parent itself has no storage and its replication identity plays no role in logical replication. Logical decoding always operates on the leaf partitions, and their replication identities determine whether UPDATE/DELETE can be replicated safely.This gap leads to several problems:
* The parent table’s replica identity is effectively irrelevant during logical replication, since it never stores tuples or produces WAL.
When we use row filters, if publish_via_partition_root option of
publication is true, the root partitioned table's row filter is used.
I think this would then refer RI of partitioned table for validity of
row filter. Please see docs [1] (There can be a case where a
subscription combines multiple publications. If a partitioned table is
published by any subscribed publications which set
publish_via_partition_root = true, changes on this partitioned table
(or on its partitions) will be published using the identity and schema
of this partitioned table rather than that of the individual
partitions. This parameter also affects how row filters and column
lists are chosen for partitions; see below for details.) for more
details.I have not tested it but you can once try to see how it behaves.
The other point is what if one of the partition already has RI defined
to a different value than what is defined for parent table?[1] - https://www.postgresql.org/docs/devel/sql-createpublication.html
--
With Regards,
Amit Kapila.
Hi Amit,
Thanks for pointing out that my assumption of “RI of parent is not used” is not always true.
I agree that automatic-cascade will introduce a lot of complexities. To ensure the backward-compatibility, how about to extend the ALTER TABLE syntax like:
```
ALTER TABLE <root> REPLICA IDENTITY <type> [CASCADE | FORCE CASCADE]
```
So, that the current syntax will behave the same as usual, and
With CASCADE
============
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated unless 3
3. If any child’s RI is different from the root's RI, fail out, no change happens
With CASCADE FORCE
===================
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated, prints a warning message when a child’s RI is different from root’s RI
"ALTER TABLE parent REPLICA IDENTITY” is a PG specific syntax, so the change won’t break the SQL standard. And “CASCADE” is known keyword that has been used in many SQL commands.
I can see the usefulness of “CASCADE” when a partitioned table has many partitions. A single command will be able to update all partitions’ RI.
What do you think?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Fri, Dec 12, 2025 at 9:28 AM Chao Li <li.evan.chao@gmail.com> wrote:
Hi Amit,
Thanks for pointing out that my assumption of “RI of parent is not used” is not always true.
I agree that automatic-cascade will introduce a lot of complexities. To ensure the backward-compatibility, how about to extend the ALTER TABLE syntax like:
```
ALTER TABLE <root> REPLICA IDENTITY <type> [CASCADE | FORCE CASCADE]
```
CASCADE is used for dependent objects, so I don't think using it will
be appropriate in this context. However, the INHERIT (NO INHERIT)
could be used. We already use them for constraints, see ALTER TABLE
... ALTER CONSTRAINT syntax in docs.
So, that the current syntax will behave the same as usual, and
With CASCADE
============
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated unless 3
3. If any child’s RI is different from the root's RI, fail out, no change happensWith CASCADE FORCE
===================
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated, prints a warning message when a child’s RI is different from root’s RI
I think you can try to experiment with CHECK or NOT NULL constraint
behavior for similar cases in case of partition tables.
BTW, did you get this use case in the field or just browsing docs, you
thought it would be useful to have such a feature?
--
With Regards,
Amit Kapila.
Hi Amit,
Thank you very much for the guidance.
On Dec 13, 2025, at 19:10, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Dec 12, 2025 at 9:28 AM Chao Li <li.evan.chao@gmail.com> wrote:
Hi Amit,
Thanks for pointing out that my assumption of “RI of parent is not used” is not always true.
I agree that automatic-cascade will introduce a lot of complexities. To ensure the backward-compatibility, how about to extend the ALTER TABLE syntax like:
```
ALTER TABLE <root> REPLICA IDENTITY <type> [CASCADE | FORCE CASCADE]
```CASCADE is used for dependent objects, so I don't think using it will
be appropriate in this context. However, the INHERIT (NO INHERIT)
could be used. We already use them for constraints, see ALTER TABLE
... ALTER CONSTRAINT syntax in docs.So, that the current syntax will behave the same as usual, and
With CASCADE
============
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated unless 3
3. If any child’s RI is different from the root's RI, fail out, no change happensWith CASCADE FORCE
===================
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated, prints a warning message when a child’s RI is different from root’s RII think you can try to experiment with CHECK or NOT NULL constraint
behavior for similar cases in case of partition tables.BTW, did you get this use case in the field or just browsing docs, you
thought it would be useful to have such a feature?
The main problem I am trying to solve is [1]/messages/by-id/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com, where you are already in the thread. This is a real pain point reported by our users and field teams. While working on [1]/messages/by-id/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com, I noticed this additional issue during my own tests. I then discussed it with our field teams, and they confirmed that such a feature would be very helpful in practice. We have many deployments where a single partitioned table has several thousands of partitions, and having a fast, single command to update the replica identity of all partitions would significantly simplify operations.
I also confirmed one thing with the field teams: across our deployments (my company has 100K+ deployments in China), they have never seen a case where partitions under the same parent/root use different replica identities. In theory, this is allowed, since RI can be set per partition, but in practice I am not sure whether such a use case really exists.
Currently, replica identity is not inheritable for partitions. I verified this behavior: if I create a partitioned table, alter its RI to FULL, and then create a new partition, the new partition still uses DEFAULT. If we keep this behavior, we can easily run into a scenario like this:
* create a partitioned table with 10 partitions
* ALTER TABLE <parent> REPLICA IDENTITY FULL INHERIT -- assume this feature exists
* create 5 new partitions
* ALTER TABLE <parent> REPLICA IDENTITY FULL INHERIT -- conflict occurs
In this case, a conflict occurs because the newly created partitions still have DEFAULT RI, but this is not the user’s intention.
From this perspective, when a new partition is created, it should automatically inherit the parent’s RI. With that behavior, a “FORCE” option would rarely be needed, because having one partition use a different RI from its siblings should be an uncommon case.
Based on this, I imagine the feature roughly like this:
* When a new partition is created, it inherits its parent’s RI
* ALTER TABLE <table_name> REPLICA IDENTITY [ INHERIT | NO INHERIT ] -- error out on conflicts
This leads to a couple of follow-up questions:
* Should RI be switchable between “inheritable” and “non-inheritable”, similar to constraints? IMO, no. RI is much simpler than constraints. For constraints, parent–child relationships exist between tables with potentially different structures, so it is natural that child tables might have different constraints. RI, however, only applies to partitioned tables, where partitions must share the same structure as the parent. In practice, it seems rare for partitions to intentionally use a different RI than the parent.
* Should publish_via_partition_root in publications affect this feature? IMO, no. A table can belong to multiple publications, and different publications may have different publish_via_partition_root settings.
[1]: /messages/by-id/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Mon, Dec 15, 2025 at 12:44 PM Chao Li <li.evan.chao@gmail.com> wrote:
Thank you very much for the guidance.
On Dec 13, 2025, at 19:10, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Dec 12, 2025 at 9:28 AM Chao Li <li.evan.chao@gmail.com> wrote:
Hi Amit,
Thanks for pointing out that my assumption of “RI of parent is not used” is not always true.
I agree that automatic-cascade will introduce a lot of complexities. To ensure the backward-compatibility, how about to extend the ALTER TABLE syntax like:
```
ALTER TABLE <root> REPLICA IDENTITY <type> [CASCADE | FORCE CASCADE]
```CASCADE is used for dependent objects, so I don't think using it will
be appropriate in this context. However, the INHERIT (NO INHERIT)
could be used. We already use them for constraints, see ALTER TABLE
... ALTER CONSTRAINT syntax in docs.So, that the current syntax will behave the same as usual, and
With CASCADE
============
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated unless 3
3. If any child’s RI is different from the root's RI, fail out, no change happensWith CASCADE FORCE
===================
1. Root's RI updated
2. All children (including middle partitioned tables and leaf tables) RI updated, prints a warning message when a child’s RI is different from root’s RII think you can try to experiment with CHECK or NOT NULL constraint
behavior for similar cases in case of partition tables.BTW, did you get this use case in the field or just browsing docs, you
thought it would be useful to have such a feature?The main problem I am trying to solve is [1], where you are already in the thread. This is a real pain point reported by our users and field teams. While working on [1], I noticed this additional issue during my own tests. I then discussed it with our field teams, and they confirmed that such a feature would be very helpful in practice. We have many deployments where a single partitioned table has several thousands of partitions, and having a fast, single command to update the replica identity of all partitions would significantly simplify operations.
I also confirmed one thing with the field teams: across our deployments (my company has 100K+ deployments in China), they have never seen a case where partitions under the same parent/root use different replica identities. In theory, this is allowed, since RI can be set per partition, but in practice I am not sure whether such a use case really exists.
Currently, replica identity is not inheritable for partitions. I verified this behavior: if I create a partitioned table, alter its RI to FULL, and then create a new partition, the new partition still uses DEFAULT. If we keep this behavior, we can easily run into a scenario like this:
* create a partitioned table with 10 partitions
* ALTER TABLE <parent> REPLICA IDENTITY FULL INHERIT -- assume this feature exists
* create 5 new partitions
* ALTER TABLE <parent> REPLICA IDENTITY FULL INHERIT -- conflict occursIn this case, a conflict occurs because the newly created partitions still have DEFAULT RI, but this is not the user’s intention.
From this perspective, when a new partition is created, it should automatically inherit the parent’s RI. With that behavior, a “FORCE” option would rarely be needed, because having one partition use a different RI from its siblings should be an uncommon case.
Based on this, I imagine the feature roughly like this:
* When a new partition is created, it inherits its parent’s RI
* ALTER TABLE <table_name> REPLICA IDENTITY [ INHERIT | NO INHERIT ] -- error out on conflicts
Sounds reasonable to me.
This leads to a couple of follow-up questions:
* Should RI be switchable between “inheritable” and “non-inheritable”, similar to constraints? IMO, no. RI is much simpler than constraints. For constraints, parent–child relationships exist between tables with potentially different structures, so it is natural that child tables might have different constraints. RI, however, only applies to partitioned tables, where partitions must share the same structure as the parent. In practice, it seems rare for partitions to intentionally use a different RI than the parent.
I see your point but I think we should provide resetting the option
unless it is too complex or not feasible.
* Should publish_via_partition_root in publications affect this feature? IMO, no. A table can belong to multiple publications, and different publications may have different publish_via_partition_root settings.
I also don't think so.
--
With Regards,
Amit Kapila.
On Tue, Dec 16, 2025, at 7:12 AM, Amit Kapila wrote:
On Mon, Dec 15, 2025 at 12:44 PM Chao Li <li.evan.chao@gmail.com> wrote:
From this perspective, when a new partition is created, it should automatically inherit the parent’s RI. With that behavior, a “FORCE” option would rarely be needed, because having one partition use a different RI from its siblings should be an uncommon case.
Based on this, I imagine the feature roughly like this:
* When a new partition is created, it inherits its parent’s RI
* ALTER TABLE <table_name> REPLICA IDENTITY [ INHERIT | NO INHERIT ] -- error out on conflictsSounds reasonable to me.
+1.
This leads to a couple of follow-up questions:
* Should RI be switchable between “inheritable” and “non-inheritable”, similar to constraints? IMO, no. RI is much simpler than constraints. For constraints, parent–child relationships exist between tables with potentially different structures, so it is natural that child tables might have different constraints. RI, however, only applies to partitioned tables, where partitions must share the same structure as the parent. In practice, it seems rare for partitions to intentionally use a different RI than the parent.
I see your point but I think we should provide resetting the option
unless it is too complex or not feasible.
+1.
ALTER TABLE ...
REPLICA IDENTITY { DEFAULT [ INHERIT | NO INHERIT ] |
USING INDEX index_name |
FULL [ INHERIT | NO INHERIT ] |
NOTHING [ INHERIT | NO INHERIT ] }
It doesn't make sense to have the inheritance property for INDEX. Although the
natural default value is NO INHERIT to preserve backward compatibility, I
wondering if we use INHERIT as default. The main advantage is usability as Chao
Li already mentioned. Is there any cases that having a different replica
identity from parent/partitioned table makes sense?
--
Euler Taveira
EDB https://www.enterprisedb.com/
Attachments:
Hi Amit and Euler,
Thank you both for all your advice. Here comes my first implementation of
INHERIT in the attached v2 patch.
On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira <euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is usability
as Chao
Li already mentioned. Is there any cases that having a different replica
identity from parent/partitioned table makes sense?
We can leave this topic open for discussion. In my current implementation,
NO INHERIT is still the default. But if we decide to switch the default, I
can add a new commit that should include only 1 line code change in gram.y
and a tiny doc update.
0001 - when a new partition is created, use the parent's replication
identity
0002 - add INHERIT | NO INHERIT
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
v2-0002-Add-INHERIT-NO-INHERIT-options-to-ALTER-TABLE-REP.patchapplication/octet-stream; name=v2-0002-Add-INHERIT-NO-INHERIT-options-to-ALTER-TABLE-REP.patchDownload
From 0c77fd5bb62715cd179ec26b61a0e9d782fe9d59 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Wed, 17 Dec 2025 15:39:23 +0800
Subject: [PATCH v2 2/2] =?UTF-8?q?Add=20INHERIT=20/=20NO=20INHERIT=20optio?=
=?UTF-8?q?ns=20to=20ALTER=20TABLE=20=E2=80=A6=20REPLICA=20IDENTITY?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Extend ALTER TABLE … REPLICA IDENTITY with optional INHERIT and
NO INHERIT clauses for partitioned tables.
When INHERIT is specified, the replica identity change is propagated to
all existing partitions in the partition hierarchy, provided that each
partition already has the same replica identity setting as the parent.
If any partition differs, the command fails to avoid silently overriding
per-partition configuration. NO INHERIT (the default) changes only the
parent table and leaves existing partitions unchanged.
The INHERIT option is only allowed for partitioned tables and is not
supported for REPLICA IDENTITY USING INDEX, since index-based replica
identity is inherently per-table.
Documentation is updated to describe the new syntax and semantics, and
regression tests are added to cover valid propagation, error cases, and
default NO INHERIT behavior.
Author: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 14 +++-
src/backend/commands/tablecmds.c | 42 +++++++++++
src/backend/parser/gram.y | 17 ++++-
src/include/nodes/parsenodes.h | 2 +
.../regress/expected/replica_identity.out | 74 +++++++++++++++++++
src/test/regress/sql/replica_identity.sql | 46 ++++++++++++
6 files changed, 191 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fb9140c8794..ad2383b1776 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -93,7 +93,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
OF <replaceable class="parameter">type_name</replaceable>
NOT OF
OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
- REPLICA IDENTITY { DEFAULT | USING INDEX <replaceable class="parameter">index_name</replaceable> | FULL | NOTHING }
+ REPLICA IDENTITY { DEFAULT [ INHERIT | NO INHERIT ] |
+ USING INDEX <replaceable class="parameter">index_name</replaceable> |
+ FULL [ INHERIT | NO INHERIT ] |
+ NOTHING [ INHERIT | NO INHERIT ] }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -970,6 +973,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
attached with <command>ALTER TABLE ... ATTACH PARTITION</command> inherit
that replica identity, though it can still be changed for a partition
afterwards.
+ </para>
+ <para>
+ The optional <literal>INHERIT</literal> clause (only valid
+ for partitioned tables) applies the
+ change to all existing partitions that already match the parent's replica
+ identity; if any partition has a different replica identity, the command
+ fails. <literal>NO INHERIT</literal> (the default) leaves existing
+ partitions unchanged. Replica identity set via <literal>USING
+ INDEX</literal> cannot specify inheritance.
<variablelist>
<varlistentry id="sql-altertable-replica-identity-default">
<term><literal>DEFAULT</literal></term>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b1a00ed477..54413f65a8e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18519,6 +18519,48 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
Relation indexRel;
int key;
+ if (stmt->inherit)
+ {
+ List *partRelIds = NIL;
+
+ /* Assert that we're not trying to set an index-based replica identity */
+ Assert(stmt->identity_type != REPLICA_IDENTITY_INDEX);
+
+ if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("inherit option is only valid for partitioned tables")));
+
+ partRelIds = GetPubPartitionOptionRelations(partRelIds, PUBLICATION_PART_ALL,
+ RelationGetRelid(rel));
+ foreach_oid(partRelOid, partRelIds)
+ {
+ Relation partRel;
+
+ /*
+ * Because we used PUBLICATION_PART_ALL, partRelIds contains OIDs
+ * of the entire parition tree, so we need to skip the parent
+ * table itself.
+ */
+ if (partRelOid == RelationGetRelid(rel))
+ continue;
+
+ partRel = relation_open(partRelOid, ExclusiveLock);
+
+ /* Check if the partition has a different replica identity setting */
+ if (partRel->rd_rel->relreplident != rel->rd_rel->relreplident)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("partition %s has a different replica identity setting",
+ quote_identifier(RelationGetRelationName(partRel)))));
+ }
+
+ relation_mark_replica_identity(partRel, stmt->identity_type, InvalidOid, true);
+ relation_close(partRel, ExclusiveLock);
+ }
+ }
+
if (stmt->identity_type == REPLICA_IDENTITY_DEFAULT)
{
relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 28f4e11e30f..aa2e21a27eb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -337,6 +337,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
replica_identity partition_cmd index_partition_cmd
+%type <boolean> opt_replica_identity_inherit
%type <list> alter_table_cmds alter_type_cmds
%type <list> alter_identity_column_option_list
%type <defelt> alter_identity_column_option
@@ -3147,27 +3148,30 @@ alter_using:
;
replica_identity:
- NOTHING
+ NOTHING opt_replica_identity_inherit
{
ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt);
n->identity_type = REPLICA_IDENTITY_NOTHING;
+ n->inherit = $2;
n->name = NULL;
$$ = (Node *) n;
}
- | FULL
+ | FULL opt_replica_identity_inherit
{
ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt);
n->identity_type = REPLICA_IDENTITY_FULL;
+ n->inherit = $2;
n->name = NULL;
$$ = (Node *) n;
}
- | DEFAULT
+ | DEFAULT opt_replica_identity_inherit
{
ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt);
n->identity_type = REPLICA_IDENTITY_DEFAULT;
+ n->inherit = $2;
n->name = NULL;
$$ = (Node *) n;
}
@@ -3176,11 +3180,18 @@ replica_identity:
ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt);
n->identity_type = REPLICA_IDENTITY_INDEX;
+ n->inherit = false;
n->name = $3;
$$ = (Node *) n;
}
;
+opt_replica_identity_inherit:
+ /* EMPTY */ { $$ = false; }
+ | INHERIT { $$ = true; }
+ | NO INHERIT { $$ = false; }
+ ;
+
reloptions:
'(' reloption_list ')' { $$ = $2; }
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index bc7adba4a0f..a569e7d3754 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2545,6 +2545,8 @@ typedef struct ReplicaIdentityStmt
{
NodeTag type;
char identity_type;
+ bool inherit; /* apply to child tables, only valid for
+ * identity_type 'd', 'f' and 'n' */
char *name;
} ReplicaIdentityStmt;
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index 9766bd5af89..59ec371d205 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -357,6 +357,76 @@ SELECT relreplident FROM pg_class
f
(1 row)
+-- INHERIT option is only valid for partitioned tables
+CREATE TABLE test_replica_identity8 (a int);
+ALTER TABLE test_replica_identity8 REPLICA IDENTITY FULL INHERIT;
+ERROR: inherit option is only valid for partitioned tables
+-- INHERIT propagates replica identity change to partitions when they match
+CREATE TABLE test_replica_identity9 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity9_p1 PARTITION OF test_replica_identity9
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity9_p2 PARTITION OF test_replica_identity9
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity9 REPLICA IDENTITY FULL INHERIT;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9_p1'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9_p2'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+-- INHERIT fails if a partition already has a different replica identity
+CREATE TABLE test_replica_identity10 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity10_p1 PARTITION OF test_replica_identity10
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity10_p2 PARTITION OF test_replica_identity10
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity10_p2 REPLICA IDENTITY FULL;
+ALTER TABLE test_replica_identity10 REPLICA IDENTITY NOTHING INHERIT;
+ERROR: partition test_replica_identity10_p2 has a different replica identity setting
+-- NO INHERIT changes only the parent replica identity
+CREATE TABLE test_replica_identity11 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity11_p1 PARTITION OF test_replica_identity11
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity11_p2 PARTITION OF test_replica_identity11
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity11_p1 REPLICA IDENTITY FULL;
+ALTER TABLE test_replica_identity11 REPLICA IDENTITY NOTHING NO INHERIT;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11'::regclass;
+ relreplident
+--------------
+ n
+(1 row)
+
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11_p1'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11_p2'::regclass;
+ relreplident
+--------------
+ d
+(1 row)
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
@@ -366,3 +436,7 @@ DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
DROP TABLE test_replica_identity6;
DROP TABLE test_replica_identity7;
+DROP TABLE test_replica_identity8;
+DROP TABLE test_replica_identity9;
+DROP TABLE test_replica_identity10;
+DROP TABLE test_replica_identity11;
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 7c0c7365c26..77038f8480f 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -173,6 +173,48 @@ ALTER TABLE test_replica_identity7_p ATTACH PARTITION test_replica_identity7_lea
SELECT relreplident FROM pg_class
WHERE oid = 'test_replica_identity7_leaf'::regclass;
+-- INHERIT option is only valid for partitioned tables
+CREATE TABLE test_replica_identity8 (a int);
+ALTER TABLE test_replica_identity8 REPLICA IDENTITY FULL INHERIT;
+
+-- INHERIT propagates replica identity change to partitions when they match
+CREATE TABLE test_replica_identity9 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity9_p1 PARTITION OF test_replica_identity9
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity9_p2 PARTITION OF test_replica_identity9
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity9 REPLICA IDENTITY FULL INHERIT;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9'::regclass;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9_p1'::regclass;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity9_p2'::regclass;
+
+-- INHERIT fails if a partition already has a different replica identity
+CREATE TABLE test_replica_identity10 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity10_p1 PARTITION OF test_replica_identity10
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity10_p2 PARTITION OF test_replica_identity10
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity10_p2 REPLICA IDENTITY FULL;
+ALTER TABLE test_replica_identity10 REPLICA IDENTITY NOTHING INHERIT;
+
+-- NO INHERIT changes only the parent replica identity
+CREATE TABLE test_replica_identity11 (a int) PARTITION BY LIST (a);
+CREATE TABLE test_replica_identity11_p1 PARTITION OF test_replica_identity11
+ FOR VALUES IN (1);
+CREATE TABLE test_replica_identity11_p2 PARTITION OF test_replica_identity11
+ FOR VALUES IN (2);
+ALTER TABLE test_replica_identity11_p1 REPLICA IDENTITY FULL;
+ALTER TABLE test_replica_identity11 REPLICA IDENTITY NOTHING NO INHERIT;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11'::regclass;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11_p1'::regclass;
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity11_p2'::regclass;
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
@@ -182,3 +224,7 @@ DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
DROP TABLE test_replica_identity6;
DROP TABLE test_replica_identity7;
+DROP TABLE test_replica_identity8;
+DROP TABLE test_replica_identity9;
+DROP TABLE test_replica_identity10;
+DROP TABLE test_replica_identity11;
--
2.39.5 (Apple Git-154)
v2-0001-Inherit-replica-identity-for-new-and-attached-par.patchapplication/octet-stream; name=v2-0001-Inherit-replica-identity-for-new-and-attached-par.patchDownload
From cf42dee8571e7c584cd1f7435ebe0d08f08be4b9 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Thu, 11 Dec 2025 17:03:37 +0800
Subject: [PATCH v2 1/2] Inherit replica identity for new and attached
partitions
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
When a replica identity is set on a partitioned table, newly created
partitions and tables attached as partitions should inherit the parent’s
replica identity setting. Previously, such relations kept the default
replica identity, which could lead to inconsistent behavior in logical
replication.
Update StorePartitionBound() to copy relreplident from the parent
relation when marking a table as a partition. This ensures consistent
replica identity semantics across partition hierarchies, including
multi-level partitioning.
Document the behavior in ALTER TABLE ... REPLICA IDENTITY, and add
regression tests covering:
* partitions created with CREATE TABLE ... PARTITION OF
* tables attached via ALTER TABLE ... ATTACH PARTITION
* inheritance through partitioned children and grandchildren
Author: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 5 ++
src/backend/catalog/heap.c | 8 ++-
.../regress/expected/replica_identity.out | 69 +++++++++++++++++++
src/test/regress/sql/replica_identity.sql | 41 +++++++++++
4 files changed, 121 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9abd8037f28..fb9140c8794 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -965,6 +965,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
from the new value; however, if the old value is stored externally, it is
always logged regardless of whether it changed.
This option has no effect except when logical replication is in use.
+ When a replica identity is set on a partitioned table, new partitions
+ created with <command>CREATE TABLE ... PARTITION OF</command> and tables
+ attached with <command>ALTER TABLE ... ATTACH PARTITION</command> inherit
+ that replica identity, though it can still be changed for a partition
+ afterwards.
<variablelist>
<varlistentry id="sql-altertable-replica-identity-default">
<term><literal>DEFAULT</literal></term>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 265cc3e5fbf..4ffc9de064e 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -4036,8 +4036,9 @@ RemovePartitionKeyByRelId(Oid relid)
/*
* StorePartitionBound
- * Update pg_class tuple of rel to store the partition bound and set
- * relispartition to true
+ * Update pg_class tuple of rel to store the partition bound, set
+ * relreplident to the same of the parent and set relispartition
+ * to true
*
* If this is the default partition, also update the default partition OID in
* pg_partitioned_table.
@@ -4090,6 +4091,9 @@ StorePartitionBound(Relation rel, Relation parent, PartitionBoundSpec *bound)
/* Also set the flag */
((Form_pg_class) GETSTRUCT(newtuple))->relispartition = true;
+ /* Copy replication identity from parent if needed */
+ ((Form_pg_class) GETSTRUCT(newtuple))->relreplident = parent->rd_rel->relreplident;
+
/*
* We already checked for no inheritance children, but reset
* relhassubclass in case it was left over.
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index b9b8dde018f..9766bd5af89 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -290,6 +290,73 @@ ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey;
ERROR: constraint "test_replica_identity5_pkey" of relation "test_replica_identity5" does not exist
ALTER TABLE test_replica_identity5 ALTER b DROP NOT NULL;
ERROR: column "b" is in index used as replica identity
+-- New partitions inherit parent's replica identity setting
+CREATE TABLE test_replica_identity6 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY FULL;
+CREATE TABLE test_replica_identity6_p1 PARTITION OF test_replica_identity6
+ FOR VALUES IN (1);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p1'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+CREATE TABLE test_replica_identity6_p2 (a int, b int);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p2'::regclass;
+ relreplident
+--------------
+ d
+(1 row)
+
+ALTER TABLE test_replica_identity6 ATTACH PARTITION test_replica_identity6_p2
+ FOR VALUES IN (2);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p2'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+-- Partitioned child tables and their grandchildren inherit replica identity
+CREATE TABLE test_replica_identity7 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE test_replica_identity7 REPLICA IDENTITY FULL;
+CREATE TABLE test_replica_identity7_p PARTITION OF test_replica_identity7
+ FOR VALUES IN (1) PARTITION BY LIST (b);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_p'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+CREATE TABLE test_replica_identity7_p1 PARTITION OF test_replica_identity7_p
+ FOR VALUES IN (1);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_p1'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
+CREATE TABLE test_replica_identity7_leaf (a int, b int);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_leaf'::regclass;
+ relreplident
+--------------
+ d
+(1 row)
+
+ALTER TABLE test_replica_identity7_p ATTACH PARTITION test_replica_identity7_leaf
+ FOR VALUES IN (2);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_leaf'::regclass;
+ relreplident
+--------------
+ f
+(1 row)
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
@@ -297,3 +364,5 @@ DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity5;
DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
+DROP TABLE test_replica_identity6;
+DROP TABLE test_replica_identity7;
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 30daec05b71..7c0c7365c26 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -134,6 +134,45 @@ ALTER TABLE test_replica_identity5 ALTER b SET NOT NULL;
ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey;
ALTER TABLE test_replica_identity5 ALTER b DROP NOT NULL;
+-- New partitions inherit parent's replica identity setting
+CREATE TABLE test_replica_identity6 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY FULL;
+
+CREATE TABLE test_replica_identity6_p1 PARTITION OF test_replica_identity6
+ FOR VALUES IN (1);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p1'::regclass;
+
+CREATE TABLE test_replica_identity6_p2 (a int, b int);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p2'::regclass;
+ALTER TABLE test_replica_identity6 ATTACH PARTITION test_replica_identity6_p2
+ FOR VALUES IN (2);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity6_p2'::regclass;
+
+-- Partitioned child tables and their grandchildren inherit replica identity
+CREATE TABLE test_replica_identity7 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE test_replica_identity7 REPLICA IDENTITY FULL;
+
+CREATE TABLE test_replica_identity7_p PARTITION OF test_replica_identity7
+ FOR VALUES IN (1) PARTITION BY LIST (b);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_p'::regclass;
+
+CREATE TABLE test_replica_identity7_p1 PARTITION OF test_replica_identity7_p
+ FOR VALUES IN (1);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_p1'::regclass;
+
+CREATE TABLE test_replica_identity7_leaf (a int, b int);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_leaf'::regclass;
+ALTER TABLE test_replica_identity7_p ATTACH PARTITION test_replica_identity7_leaf
+ FOR VALUES IN (2);
+SELECT relreplident FROM pg_class
+ WHERE oid = 'test_replica_identity7_leaf'::regclass;
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
@@ -141,3 +180,5 @@ DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity5;
DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
+DROP TABLE test_replica_identity6;
+DROP TABLE test_replica_identity7;
--
2.39.5 (Apple Git-154)
On Wednesday, December 17, 2025 3:56 PM Chao Li <li.evan.chao@gmail.com> wrote:
Thank you both for all your advice. Here comes my first implementation of
INHERIT in the attached v2 patch.On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira <mailto:euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is usability as
Chao Li already mentioned. Is there any cases that having a different
replica identity from parent/partitioned table makes sense?We can leave this topic open for discussion. In my current implementation, NO
INHERIT is still the default. But if we decide to switch the default, I can add
a new commit that should include only 1 line code change in gram.y and a tiny
doc update.0001 - when a new partition is created, use the parent's replication identity
0002 - add INHERIT | NO INHERIT
Thanks for updating the patches.
I think there are several design considerations for this proposal:
1) Since the index names can vary across different partitions, what should be the
expected behavior if a new partition cannot identify the same replica identity
key as the root partitioned table?
2) Should we simply use the ONLY keyword to determine whether to propagate the
replica identity to partitions instead of adding [NOT] INHERIT? For example, if
a user specifies ONLY, it changes the identity of the parent table, and any
newly created partitions will adopt this new identity. However, the identities
of existing partitions remain unchanged.
3) There have been previous discussions on similar proposals[1]/messages/by-id/201902041630.gpadougzab7v@alvherre.pgsql[2]/messages/by-id/OSBPR01MB2982A2738F16722899A50082FECB0@OSBPR01MB2982.jpnprd01.prod.outlook.com. It might be
beneficial to review those debates to see whether any old issues or arguments
are pertinent to this proposal.
[1]: /messages/by-id/201902041630.gpadougzab7v@alvherre.pgsql
[2]: /messages/by-id/OSBPR01MB2982A2738F16722899A50082FECB0@OSBPR01MB2982.jpnprd01.prod.outlook.com
Best Regards,
Hou zj
On Dec 17, 2025, at 16:48, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> wrote:
On Wednesday, December 17, 2025 3:56 PM Chao Li <li.evan.chao@gmail.com> wrote:
Thank you both for all your advice. Here comes my first implementation of
INHERIT in the attached v2 patch.On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira <mailto:euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is usability as
Chao Li already mentioned. Is there any cases that having a different
replica identity from parent/partitioned table makes sense?We can leave this topic open for discussion. In my current implementation, NO
INHERIT is still the default. But if we decide to switch the default, I can add
a new commit that should include only 1 line code change in gram.y and a tiny
doc update.0001 - when a new partition is created, use the parent's replication identity
0002 - add INHERIT | NO INHERIT
Hi Zhijie,
Thanks for your feedback and linked information. I think this patch is avoiding the hard problem of “index” RI.
I think there are several design considerations for this proposal:
1) Since the index names can vary across different partitions, what should be the
expected behavior if a new partition cannot identify the same replica identity
key as the root partitioned table?
Index RI is skipped in this patch. INHERT only works for DEFAULT, FULL and NONE.
2) Should we simply use the ONLY keyword to determine whether to propagate the
replica identity to partitions instead of adding [NOT] INHERIT? For example, if
a user specifies ONLY, it changes the identity of the parent table, and any
newly created partitions will adopt this new identity. However, the identities
of existing partitions remain unchanged.
The current syntax “ALTER TABLE [ONLY] table_name action”, I think here “ONLY” works in terms of inherited tables but partitions. (I surprisedly find the doc doesn’t explain “ONLY” at all.) The current behavior (without my patch) proves my understanding, “ALTER TABLE table_name REPLICA IDENTITY” only updates the parent table itself.
Given that it is not allowed to inherit a table from either a partitioned table (root/parent) or partition (leaf), reusing the “ONLY" at “ALTER TABLE” level won’t generate a conflict. But the problem is, we will have to revisit all ALTER TABLE actions to see if they can propagate to partitions when the target table is a partitioned table. In that case, scope of this patch is extremely extended.
The current approach adding INHERIT to the action “REPLICA IDENTIFY” has no global impacts. If you look at the patch, the syntax this patch uses is:
```
ALTER TABLE ...
REPLICA IDENTITY { DEFAULT [ INHERIT | NO INHERIT ] |
USING INDEX index_name |
FULL [ INHERIT | NO INHERIT ] |
NOTHING [ INHERIT | NO INHERIT ] }
```
It explicitly avoids the complexity of “USING INDEX”, thus we don’t have to mention any exceptions in docs.
Amit, what do you think?
3) There have been previous discussions on similar proposals[1][2]. It might be
beneficial to review those debates to see whether any old issues or arguments
are pertinent to this proposal.[1] /messages/by-id/201902041630.gpadougzab7v@alvherre.pgsql
[2] /messages/by-id/OSBPR01MB2982A2738F16722899A50082FECB0@OSBPR01MB2982.jpnprd01.prod.outlook.com
I read through the both threads. I think the key difference between the patch and the previous one is that this patch only adds “INHERIT” to DEFAULT/FULL/NONE, which is narrow scoped. Let’s see how the folks who involved in the previous discussion will feedback to this patch.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Dec 18, 2025, at 12:21, Chao Li <li.evan.chao@gmail.com> wrote:
2) Should we simply use the ONLY keyword to determine whether to propagate the
replica identity to partitions instead of adding [NOT] INHERIT? For example, if
a user specifies ONLY, it changes the identity of the parent table, and any
newly created partitions will adopt this new identity. However, the identities
of existing partitions remain unchanged.The current syntax “ALTER TABLE [ONLY] table_name action”, I think here “ONLY” works in terms of inherited tables but partitions. (I surprisedly find the doc doesn’t explain “ONLY” at all.) The current behavior (without my patch) proves my understanding, “ALTER TABLE table_name REPLICA IDENTITY” only updates the parent table itself.
Given that it is not allowed to inherit a table from either a partitioned table (root/parent) or partition (leaf), reusing the “ONLY" at “ALTER TABLE” level won’t generate a conflict. But the problem is, we will have to revisit all ALTER TABLE actions to see if they can propagate to partitions when the target table is a partitioned table. In that case, scope of this patch is extremely extended.
The current approach adding INHERIT to the action “REPLICA IDENTIFY” has no global impacts. If you look at the patch, the syntax this patch uses is:
```
ALTER TABLE ...
REPLICA IDENTITY { DEFAULT [ INHERIT | NO INHERIT ] |
USING INDEX index_name |
FULL [ INHERIT | NO INHERIT ] |
NOTHING [ INHERIT | NO INHERIT ] }
```
It explicitly avoids the complexity of “USING INDEX”, thus we don’t have to mention any exceptions in docs.Amit, what do you think?
I read through the doc of “ALTER TABLE” again, and got some findings.
```
name - The name (optionally schema-qualified) of an existing table to alter. If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.
```
For table name, it says "If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered.” Here, I think we can “descendant tables” as both inherited table or partition table, as they are mutually exclusive.
And for “DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER”, the doc says:
```
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
When this command is applied to a partitioned table, the states of corresponding clone triggers in the partitions are updated too, unless ONLY is specified.
```
Here, ONLY is used for partition table. From this perspective, “REPLICA IDENTITY” not propagating to children feels like a “bug”.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Thursday, December 18, 2025 12:21 PM Chao Li <li.evan.chao@gmail.com> wrote:
On Dec 17, 2025, at 16:48, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
wrote:
On Wednesday, December 17, 2025 3:56 PM Chao Li
<li.evan.chao@gmail.com> wrote:
Thank you both for all your advice. Here comes my first
implementation of INHERIT in the attached v2 patch.On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira
<mailto:euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is
usability as Chao Li already mentioned. Is there any cases that
having a different replica identity from parent/partitioned table makessense?
We can leave this topic open for discussion. In my current
implementation, NO INHERIT is still the default. But if we decide to
switch the default, I can add a new commit that should include only 1
line code change in gram.y and a tiny doc update.0001 - when a new partition is created, use the parent's replication
identity
0002 - add INHERIT | NO INHERITHi Zhijie,
Thanks for your feedback and linked information. I think this patch is avoiding
the hard problem of “index” RI.I think there are several design considerations for this proposal:
1) Since the index names can vary across different partitions, what
should be the expected behavior if a new partition cannot identify the
same replica identity key as the root partitioned table?Index RI is skipped in this patch. INHERT only works for DEFAULT, FULL and
NONE.
I personally find skipping this part to be inconsistent, particularly given the
existing disparities among ALTER TABLE commands related to partitioned table handling.
Omitting this part introduces further inconsistency within the ALTER TABLE
REPLICA IDENTITY.
That said, I understand that skipping this implementation might be necessary due
to technical challenges. It's important, however, to discuss, analyze and document the
specific difficulties associated with implementing this part, and we should
include this information in the code comments and commit message.
Best Regards,
Hou zj
On Fri, Dec 19, 2025 at 11:14 AM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:
On Thursday, December 18, 2025 12:21 PM Chao Li <li.evan.chao@gmail.com> wrote:
On Dec 17, 2025, at 16:48, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
wrote:
On Wednesday, December 17, 2025 3:56 PM Chao Li
<li.evan.chao@gmail.com> wrote:
Thank you both for all your advice. Here comes my first
implementation of INHERIT in the attached v2 patch.On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira
<mailto:euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is
usability as Chao Li already mentioned. Is there any cases that
having a different replica identity from parent/partitioned table makessense?
We can leave this topic open for discussion. In my current
implementation, NO INHERIT is still the default. But if we decide to
switch the default, I can add a new commit that should include only 1
line code change in gram.y and a tiny doc update.0001 - when a new partition is created, use the parent's replication
identity
0002 - add INHERIT | NO INHERITHi Zhijie,
Thanks for your feedback and linked information. I think this patch is avoiding
the hard problem of “index” RI.I think there are several design considerations for this proposal:
1) Since the index names can vary across different partitions, what
should be the expected behavior if a new partition cannot identify the
same replica identity key as the root partitioned table?Index RI is skipped in this patch. INHERT only works for DEFAULT, FULL and
NONE.I personally find skipping this part to be inconsistent, particularly given the
existing disparities among ALTER TABLE commands related to partitioned table handling.
Omitting this part introduces further inconsistency within the ALTER TABLE
REPLICA IDENTITY.
Fair point. I think one should summarize the previous discussions with
key differences and where the previous patch got stuck. Then, it would
be good to get some feedback from the people involved previously. If
there is an agreement that we can do INHERIT stuff for specific parts
then fine, otherwise, I think we need to address the index part as
well.
--
With Regards,
Amit Kapila.
On Dec 22, 2025, at 14:26, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Dec 19, 2025 at 11:14 AM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:On Thursday, December 18, 2025 12:21 PM Chao Li <li.evan.chao@gmail.com> wrote:
On Dec 17, 2025, at 16:48, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
wrote:
On Wednesday, December 17, 2025 3:56 PM Chao Li
<li.evan.chao@gmail.com> wrote:
Thank you both for all your advice. Here comes my first
implementation of INHERIT in the attached v2 patch.On Wed, Dec 17, 2025 at 8:11 AM Euler Taveira
<mailto:euler@eulerto.com> wrote:
I wondering if we use INHERIT as default. The main advantage is
usability as Chao Li already mentioned. Is there any cases that
having a different replica identity from parent/partitioned table makessense?
We can leave this topic open for discussion. In my current
implementation, NO INHERIT is still the default. But if we decide to
switch the default, I can add a new commit that should include only 1
line code change in gram.y and a tiny doc update.0001 - when a new partition is created, use the parent's replication
identity
0002 - add INHERIT | NO INHERITHi Zhijie,
Thanks for your feedback and linked information. I think this patch is avoiding
the hard problem of “index” RI.I think there are several design considerations for this proposal:
1) Since the index names can vary across different partitions, what
should be the expected behavior if a new partition cannot identify the
same replica identity key as the root partitioned table?Index RI is skipped in this patch. INHERT only works for DEFAULT, FULL and
NONE.I personally find skipping this part to be inconsistent, particularly given the
existing disparities among ALTER TABLE commands related to partitioned table handling.
Omitting this part introduces further inconsistency within the ALTER TABLE
REPLICA IDENTITY.Fair point. I think one should summarize the previous discussions with
key differences and where the previous patch got stuck. Then, it would
be good to get some feedback from the people involved previously. If
there is an agreement that we can do INHERIT stuff for specific parts
then fine, otherwise, I think we need to address the index part as
well.
[1] /messages/by-id/201902041630.gpadougzab7v@alvherre.pgsql
[2] /messages/by-id/OSBPR01MB2982A2738F16722899A50082FECB0@OSBPR01MB2982.jpnprd01.prod.outlook.com
Sure, let me try to summarize the two discussions.
For [1], key participants included Álvaro Herrera (patch author), Dmitry Dolgov (reviewer/tester), Robert Haas, Simon Riggs, Michael Paquier, and Peter Eisentraut.
----
Brief summary with rough timeline:
* In 2019, Álvaro proposed a patch that essentially did the same thing as this patch. That patch also attempted to handle “index-based” replica identity, which my patch intentionally avoids.
* Dmitry tested the patch and reported issues related to attaching partitions, including index-related errors.
* Robert then pointed out that having REPLICA IDENTITY recurse was inconsistent with the behavior of other ALTER TABLE actions such as TABLESPACE, and emphasized that we should really try to think through *all* actions that might recurse to child partitions.
* Álvaro noted the use of `ALTER TABLE ONLY`, suggesting that WITHOUT ONLY could recurse to children, while ONLY would affect just the parent.
* Simon commented that recursing TABLESPACE changes could be problematic because they imply physical data rewrites.
* Robert listed several ALTER TABLE actions that lacked consistent recurse behavior (identity columns, triggers, CLUSTER, OWNER, TABLESPACE, CHECK constraints).
* This led to broader discussion about whether TABLESPACE/OWNER/etc. should recurse.
* Michael echoed support for having REPLICA IDENTITY recurse, controlled via ONLY.
* Peter pointed out that recursing ADD GENERATED AS IDENTITY / DROP IDENTITY may not be feasible.
* Álvaro wanted to proceed with the patch.
* Robert maintained that defining consistent semantics for all relevant ALTER TABLE actions should come first.
Overall, the blocker was an unresolved semantic disagreement, rather than a concrete technical objection to the patch itself. There appeared to be broad agreement that:
* New partitions should use the parent’s replica identity.
* ONLY could be used to control whether replica identity changes recurse.
However, the discussion about “semantic consistency” significantly broadened the scope, and there was no clear agreement on whether TABLESPACE/OWNER/etc. should recurse, which ultimately stalled the effort.
For [2], key participants included: Takayuki Tsunakawa (patch author), Bharath Rupireddy (reviewer), Álvaro Herrera, Michael Paquier, and Kyotaro Horiguchi.
----
Brief summary with rough timeline:
* In 2020, Takayuki proposed a patch intended to propagate ALTER TABLE SET LOGGED / UNLOGGED to partitions.
* Bharath reviewed the patch and raised a number of questions and edge cases.
* There were initial discussions about the patch mechanics and expected behavior.
* Álvaro then pointed out the strong relation to the earlier discussion in [1].
* The focus of the discussion shifted to the more fundamental question of “what is the parent?”:
* Takayuki viewed ALTER TABLE on a partitioned table as a convenient batch operation on existing partitions, with future partitions remaining independent.
* Álvaro, Michael, and Kyotaro argued that changing a property on the parent should define the default for future partitions as well.
* No clear agreement was reached on this semantic question, and the discussion expanded into broader concerns about consistency across ALTER TABLE actions.
* Takayuki withdrew the patch
Overall, [2] also fell under the umbrella of “semantic consistency”, the main discussion was not about replica identity itself.
Current situation:
----
* Resolving “semantic consistency” across ALTER TABLE actions in a single release appears to be the biggest challenge. However, addressing everything in one patch set does not seem realistic.
* The core question of “what is the parent?” from [1] remains central. That said, the discussion appeared to lean toward the view that future partitions should inherit properties from the parent.
* Different properties behave very differently. For example, propagating REPLICA IDENTITY is a metadata-only change and relatively safe, while propagating TABLESPACE implies physical data movement and is much riskier. As a result, each ALTER TABLE action may deserve its own discussion and patch set.
* The inconsistency is not limited to ALTER TABLE but also exists in CREATE TABLE behavior. For example, a new partition inherits TABLESPACE from the parent, but not REPLICA IDENTITY.
* “ALTER TABLE ONLY table_name” is commonly suggested as the mechanism to control whether changes should recurse to partitions.
How to proceed?
----
If we stop here, these inconsistencies will remain indefinitely, which I believe nobody really wants. With that in mind, I’d like to suggest a two-phase approach.
Phase 1: Document current behavior and set expectations
* Identify all ALTER TABLE actions involved in these inconsistencies.
* Update the ALTER TABLE and CREATE TABLE documentation to clearly describe the current behavior for partitioned tables, and (where appropriate) the intended or ideal behavior.
* Explicitly document the meaning of ONLY for partitioned tables, and note that some actions may behave differently, with details described in each action’s section.
Phase 2: Address actions incrementally
* Work on each ALTER TABLE action individually, recognizing that some may be straightforward while others require more design discussion, coding, and testing.
* With Phase 1 in place to set expectations, it may not be necessary to complete all actions in a single release.
* That said, it would still be desirable to keep the work bounded, for example within one or two major releases, to avoid long-term fragmentation.
I’ve included the participants from the previous discussions on CC, in case they want to comment further.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/