Re: Skipping schema changes in publication

On Tue, 3 Feb 2026 at 22:23, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Mon, 2 Feb 2026 at 17:18, Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> wrote:
> >
> > I reviewed v38-0002-handle-EXCEPT-TABLE-correctly-with-partitioned-approach-1.patch
> > patch. Here are my comments:
> >
> > 4. While testing, I noticed that the new query introduced in tablesync
> > can be invoked for "FOR TABLE". SHould we only call it for "ALL
> > TABLES" publications?
> > + if (server_version >= 190000 && !is_partition &&
> > + lrel->relkind == RELKIND_PARTITIONED_TABLE)
> > + {
> > + resetStringInfo(&cmd);
> > +
> > + /*
> > + * This query recursively traverses the inheritance (partition) tree
> > + * starting from the given table OID and determines which leaf
> > + * relations should be included for replication. Exclusion propagates
> > + * from parent to child, and a relation is also treated as excluded if
> > + * it is explicitly marked with prexcept = true in pg_publication_rel
> > + * for the specified publications. The final result returns only
> > + * non excluded leaf relations.
> > + */
> > Test:
> > Create publication for table sc1.t1 using (publish_via_partition_root
> > = true) and create subscription on it. In subscriber logs we can see
> > the logs for the new query.
> > sc1.t1 has the same structure as in comment 1.
>
> We will not know if it is a table publication or all tables
> publication from here. Also there can be a possibility of multiple
> publications. We will invoke it and handle it from the publisher to
> return appropriate tables.
>
> Thanks for the review and detailed comments. All remaining feedback
> has been addressed in the attached v39 patch.
>
> In addition, the SQL query previously used to compute the set of
> effective tables has been replaced with a C implementation. The SQL
> approach had become increasingly complex and difficult to reason
> about, especially as more publication combinations were added.
> Implementing this logic in C significantly improves readability and
> maintainability, and makes it easier to handle complex scenarios, such
> as:
> a) Multiple publications where one publication has no EXCEPT tables
> and another does.
> b) Multiple publications where one publication is an ALL TABLES
> publication with EXCEPT, while another is a table-specific
> publication.
> c) Multiple publications where none of the publications define any
> EXCEPT tables.
>
> Peter's comments from [1] and Shveta's comments form [2] will be
> addressed in the next version.
> [1] - https://www.postgresql.org/message-id/CAHut%2BPsiWwmNSuCXTWM0iPDm3yGskLts-fukELTB__rbBids-A%40mail.gmail.com
> [2] - https://www.postgresql.org/message-id/CAJpy0uAOvtMBP-oV9Tgoznt5-UsE2dzAjZW3eJmgKcU-X-vEzg%40mail.gmail.com

Hi Vignesh,

I found one bug.

For Partition structure:
sc1.t1
- sc1.child1
- sc1.child1_1
- sc1.child1_2
- sc1.child2

If we specify sc1.child1 and sc1.child2 in the EXCEPT list and
publish_via_partition_root = true,
tablesync publish all the changes. It is wrong.
Behavour of incremental sync is correct and it donot publish any changes.
Publication:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (sc1.child1,
sc1.child2) WITH (publish_via_partition_root = true);

I checked, pg_get_publication_effective_tables is not returning any row:
postgres=# SELECT schemaname, relname FROM
pg_get_publication_effective_tables(16385, ARRAY['pub1']);
schemaname | relname
------------+---------
(0 rows)

But the COPY command in tablesync is formed as:
COPY sc1.t1 (id) TO STDOUT

Same behaviour when publication is:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (sc1.child1_1,
sc1.child1_2, sc1.child2) WITH (publish_via_partition_root = true);

I think it is because, when we specify all the partitions of a
partitioned table in the EXCEPT list, the function
'fetch_remote_table_info' will have 'effective_relations' as an empty
list.
So, instead of the condition 'if (effective_relations &&
list_length(effective_relations))' it will go inside the 'else if'
condition and form the above COPY command.

One possible solution I can think of is if all the partitions of a
partitioned table is excluded using EXCEPT TABLE, we can avoid having
the partitioned table in 'pg_publication_tables' and hence the
pg_subsciption_rel will also not have the corresponding entry.
So, the issue can be avoided.

I have added the fix of the same in the latest v41 patch and added the
corresponding test in 101_test.pl file.
I have also merged the v40-0001 and v40-0002 patches to form v41-0001
patch and v41-0002 has the extended tests.

Thanks,
Shlok Kyal