> On Dec 11, 2025, at 20:43, Amit Kapila <[email protected]> wrote:
>
> On Thu, Dec 11, 2025 at 2:46 PM Chao Li <[email protected]> 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/
