| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Proposal: Cascade REPLICA IDENTITY changes to leaf partitions |
| Date: | 2025-12-11 09:15:51 |
| Message-ID: | CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-tablecmds-Cascade-REPLICA-IDENTITY-changes-to-lea.patch | application/octet-stream | 2.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chao Li | 2025-12-11 09:38:40 | Re: Report bytes and transactions actually sent downtream |
| Previous Message | shveta malik | 2025-12-11 08:57:38 | Re: Improve pg_sync_replication_slots() to wait for primary to advance |