Re: Skipping schema changes in publication

On Sun, Mar 1, 2026 at 5:52 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sat, Feb 28, 2026 at 7:37 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Sat, 28 Feb 2026 at 17:27, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > On Fri, Feb 27, 2026 at 8:44 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> > > >
> > > > On Fri, Feb 27, 2026 at 5:01 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> > > > >
> > > > > Thanks for the patches, I am still testing the patches, but sharing a
> > > > > few initial review comments.
> > > > >
> > > >
> > > > I did few tests on v51 all patches, please find further comments -
> > > >
> > > > 1. Partition Related Behavior
> > > > -----------------------------
> > > > Setup:
> > > > CREATE TABLE t_part (id int) PARTITION BY RANGE (id);
> > > > CREATE TABLE t_part_p1 PARTITION OF t_part FOR VALUES FROM (0) TO (100);
> > > > CREATE TABLE t_part_p2 (id int);
> > > > ALTER TABLE t_part ATTACH PARTITION t_part_p2 FOR VALUES FROM (100) TO (200);
> > > >
> > > > t_part
> > > > |_> t_part_p1
> > > > |_> t_part_p2
> > > >
> > > > Publication:
> > > > create publication pubp1 for ALL TABLES EXCEPT TABLE (t_part);
> > > > -- No data from all three tables is replicated, which is expected.
> > > >
> > > > 1a) DETACH t_part_p2 from parent
> > > > ALTER TABLE t_part DETACH PARTITION t_part_p2;
> > > >
> > > > When t_part_p2 is detached, it becomes a standalone table and is no
> > > > longer part of the EXCEPT hierarchy. But, replication does not start
> > > > automatically. It requires REFRESH PUBLICATION on the subscriber.
> > > > Should we add a warning/hint during DETACH command to inform users
> > > > that REFRESH PUBLICATION is required?
> > > > It may also help to update the related documentation.
> > > >
> > >
> > > This is required even when a regular (non-partitioned table) is
> > > created to let the corresponding entry populated in
> > > pg_subscription_rel. See docs[1] (REFRESH PUBLICATION ..).
> > >
> > > > ~~
> > > > 1b): Attach the partition again
> > > > ALTER TABLE t_part ATTACH PARTITION t_part_p2 FOR VALUES FROM (100) TO (200);
> > > > When the partition is attached back, replication stops immediately as
> > > > it becomes part of the excluded parent again. No REFRESH PUBLICATION
> > > > is required on the subscriber. In this case, users may not realize
> > > > that replication has stopped. Should we consider adding a warning
> > > > during ATTACH in such scenarios?
> > > > ~~~~
> > > >
> > >
> > > This is worth considering but my opinion is that we don't need it as
> > > users should be aware of such behavior. For example, today, if a
> > > publication has published a root table say tab_root and one of its
> > > partitions is detached, we should stop that partition's replication
> > > but do we give WARNING for such cases?
> > >
> > > > 2. Inherited Tables Behavior
> > > > Setup:
> > > > CREATE TABLE t_inh (id int);
> > > > CREATE TABLE t_inh_c1 (c1 int) INHERITS (t_inh);
> > > > CREATE TABLE t_inh_c2 (c2 int) INHERITS (t_inh);
> > > > t_inh
> > > > |_> t_inh_c1
> > > > |_> t_inh_c2
> > > >
> > > > Publication:
> > > > create publication pubi1 for ALL TABLES EXCEPT TABLE (t_inh);
> > > > --All three tables are not replicated.
> > > >
> > > > 2a): Remove child from parent
> > > > alter table t_inh_c1 NO INHERIT t_inh;
> > > >
> > > > \d+ t_inh shows:
> > > > Except Publications:
> > > > "pubi1"
> > > > Child tables: t_inh_c2
> > > >
> > > > But the publication still shows:
> > > > Except tables:
> > > > "public.t_inh"
> > > > "public.t_inh_c1"
> > > > "public.t_inh_c2"
> > > >
> > > > t_inh_c1 is no longer part of the excluded hierarchy, yet it remains
> > > > in the EXCEPT list and replication does not start for it.
> > > > This appears inconsistent and may be a bug.
> > > >
> > >
> > > I think after removal, it shouldn't have been shown here, so, we
> > > should see why it is happening.
> >
> > I believe this is existing behavior, and the same applies to
> > table-level publications as well.
> > For example:
> > -- Create table publication
> > create publication pubi1 for table t_inh;
> > \d+ t_inh
> > Table "public.t_inh"
> > Column | Type | Collation | Nullable | Default | Storage |
> > Compression | Stats target | Description
> > --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> > id | integer | | | | plain |
> > | |
> > Publications:
> > "pubi1"
> > Child tables: t_inh_c1,
> > t_inh_c2
> >
> > postgres=# \dRp+
> > Publication pubi1
> > Owner | All tables | All sequences | Inserts | Updates | Deletes |
> > Truncates | Generated columns | Via root
> > ---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
> > vignesh | f | f | t | t | t |
> > t | none | f
> > Tables:
> > "public.t_inh"
> > "public.t_inh_c1"
> > "public.t_inh_c2"
> >
> > --- Remove the inherits for one of the child table
> > postgres=# alter table t_inh_c1 NO INHERIT t_inh;
> > ALTER TABLE
> >
> > --- Publication will still have t_inh_c1 table in case of table publication too
> > postgres=# \dRp+
> > Publication pubi1
> > Owner | All tables | All sequences | Inserts | Updates | Deletes |
> > Truncates | Generated columns | Via root
> > ---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
> > vignesh | f | f | t | t | t |
> > t | none | f
> > Tables:
> > "public.t_inh"
> > "public.t_inh_c1"
> > "public.t_inh_c2"
> >
> > This occurs because when ONLY is not specified, the table t_inh and
> > all its descendant tables (t_inh_c1 and t_inh_c2) are added to
> > pg_publication_rel at the time the publication is created. Replication
> > then proceeds strictly based on the entries in pg_publication_rel.
> > There is no explicit parent child mapping maintained for these
> > relations in the publication catalog; each table is treated as an
> > independent entry.
> > When "ALTER TABLE t_inh_c1 NO INHERIT t_inh;" is executed later,
> > t_inh_c1 is not automatically removed from the publication. Since it
> > was added as a separate entry in pg_publication_rel, removing the
> > inheritance relationship does not affect its publication membership.
> > I'm not sure whether this is the intended design or an unintended side
> > effect. If this behavior is not expected, we can address it separately
> > and adjust the implementation accordingly.
> >
>
> So, the similar case for partitioned table works because there we add
> only root partitioned table in pg_publication_rel. I understand that
> current behaviour for inherited tables (each inherited table is
> considered separately after being added to the publication) is because
> of the existing design and should be changed separately, if required.
> I haven't checked whether it is possible to change this behavior but
> it appears that we should try to investigate and see the possibility
> of fixing it separately.
>

IMO, we do not need any change in behaviour here.

For inherited tables, publication membership is materialized at the
time the publication is created and is not dynamically derived from
the current inheritance hierarchy. Once inherited tables are added to
a publication, each table is treated as an independent publication
member going forward, regardless of subsequent changes to the
inheritance relationship.
This differs from partitioned tables in PostgreSQL. In the case of
partitioning, publication membership is derived dynamically from the
partition hierarchy. Partitions are not independent logical tables;
they are structural components of a single logical table. Thus
attaching or detaching a partition automatically impacts publication
behavior. IMO, this distinction between inherited and partitioned
tables is fundamental to their design, and thus should be preserved
as-is, including in the context of publications. Or let me know if my
understanding is not correct here.

thanks
Shveta