Re: Proposal: Cascade REPLICA IDENTITY changes to leaf partitions

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
toextend 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 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’sRI 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?
>

The main problem I am trying to solve is [1], where you are already in the thread. This is a real pain point reported
byour users and field teams. While working on [1], I noticed this additional issue during my own tests. I then
discussedit with our field teams, and they confirmed that such a feature would be very helpful in practice. We have
manydeployments where a single partitioned table has several thousands of partitions, and having a fast, single command
toupdate 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),
theyhave never seen a case where partitions under the same parent/root use different replica identities. In theory,
thisis 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
shouldbe 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
simplerthan 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
partitionedtables, where partitions must share the same structure as the parent. In practice, it seems rare for
partitionsto 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] https://postgr.es/m/CAEoWx2mMorbMwjKbT4YCsjDyL3r9Mp+z0bbK57VZ+OkJTgJQVQ@mail.gmail.com

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/