MIME-Version: 1.0
References: 
 <CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com>
 <CALj2ACVOzhs+BD+abFV2x4oKJdsDNd6SgsE7r8UjnZDCKGEckA@mail.gmail.com>
 <CAA4eK1K6Kr88d2S0zFdHRMyuoaZeNh+ktU+oigmCuD09_x_-+g@mail.gmail.com>
 <CAHut+PsvC-NezO3MJkdyEz=G1QRje2LntjwhQiEeVbmhOQuBMA@mail.gmail.com>
 <CALDaNm18VH2j8cTqfELHQ=0ZNognbGBhbHPteJenWQC6C2dueQ@mail.gmail.com>
 <CALDaNm0k_0Ccj47wzJzzPFwgQB7w=R5+Q2_nSqYrmMmjhmcRUw@mail.gmail.com>
 <CAHut+Pv_0DwyWoGQNMF+G2AGqMuJTzWQKRtmxaC+=zLTPL-Zkw@mail.gmail.com>
 <CALDaNm2-GJt2HsYTkLqQ=ecm=R-vOBw1=aM_d2EiYbz39x_cTQ@mail.gmail.com>
 <TYCPR01MB8373C3120C2B3112001ED6F1EDCF9@TYCPR01MB8373.jpnprd01.prod.outlook.com>
 <CALDaNm0iZZDB300Dez_97S8G6_RW5QpQ8ef6X3wq8tyK-8wnXQ@mail.gmail.com>
 <CAHut+PtiomM+iyAZHvb2dzfsPvRru266KuBe49hKy2n2h+m_zA@mail.gmail.com>
 <CALDaNm30KDnwX4Czi29fqLb8JBkuwqjbpj9ixwNXXox574NZqQ@mail.gmail.com>
 <CALDaNm1PfKRJsEzbKpyt=v4p3bw+_SzE+LFPsMhR5X+qs+0pPw@mail.gmail.com>
 <TYCPR01MB83730A2F1D6A5303E9C1416AEDD99@TYCPR01MB8373.jpnprd01.prod.outlook.com>
 <CALDaNm0sAU4s1KTLOEWv=rYo5dQK6uFTJn_0FKj3XG1Nv4D-qw@mail.gmail.com>
 <CALDaNm3CLRa95tpas6tEj8x58MUNDShxBNoYS+P8Uq5cryoAOw@mail.gmail.com>
 <CALDaNm0EKC3o=v+F7GneGibuCULGKkBWXmNaVB4GR9HoqD066A@mail.gmail.com>
 <CALDaNm1Z1Rmqj9s6P9ZzmrVA9F_vZ_DwwhYAJmsjqmY6dS3-hA@mail.gmail.com>
 <CAB8KJ=jJGuW=ozKmXZzKDUHZ_-J2ZYGOtJo=i2cnNbSu6=KuYg@mail.gmail.com>
 <CALDaNm1mbFP8fxHU_H1Ex4cT2Aq3n8FE79tq0TO5ThvFnDUYMA@mail.gmail.com>
 <CAB8KJ=jq4RwTs8K7pokmXQwQppP2ChVJLMSAdXaxAX+c1r+mdg@mail.gmail.com>
 <CALDaNm1mJvLni8GODebKBmyegXuZ18bLoG-Pz6H1MCX=vphCYA@mail.gmail.com>
 <CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com>
 <CALDaNm1AQZYgT0tALRrkvpP1Q+8+e7vkGCUjQ-jim1C0q3e=zA@mail.gmail.com>
 <CAA4eK1KRdAPC=5=7tQ1GW0cRwD=zaDMi+T4u_k4GxPhPY6e8BQ@mail.gmail.com>
 <OS3PR01MB5718C8BE84B862E7E0CEC29B94BD2@OS3PR01MB5718.jpnprd01.prod.outlook.com>
 <CAA4eK1KYQz7cf46_D=6VkZ4J6Y8vJ88MMi=6zm2TJXDP+V1mLg@mail.gmail.com>
 <CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@mail.gmail.com>
 <CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com>
 <CAHut+PuiaLOCkiAx9nPnjk6wTbPFvnm9T5svTuKbgwJwTdea8w@mail.gmail.com>
 <CANhcyEV_MePxgftHY65et1WdOAk70M0C7PZ1STPUO8PXHVB1YA@mail.gmail.com>
 <CAHut+Ps0hSNqrjv_jT1AuXxO-CrZue3ixE0jKsxVhtArMrkujQ@mail.gmail.com>
 <CANhcyEXX3viVpYcGHD_fzhf_f6CDQWr2+VBywrJf5zm_XiB4tg@mail.gmail.com>
 <CAJpy0uAamUiZN5LkN9y7_-zT8=OZmW846T8dfgkojFwreV8XOw@mail.gmail.com>
 <CANhcyEU9jX5wM0R3jHs5-=UFaFehcXC8KMkKow_rphHVben_Nw@mail.gmail.com>
 <CAJpy0uDWRRgg9O8dmbBsS2Lu7SJMMOmsbfYNJG4R8saPVK-EQw@mail.gmail.com>
 <CAJpy0uAB+4nB5s2P0YdFAbNhOuB1UFFL5SyogDuGNedETA+d_w@mail.gmail.com>
 <CANhcyEU9ENjbfEMRmw3MAZsOP04ORXB0Se+t_7Qcqv61sLc0dA@mail.gmail.com>
 <CAJpy0uBE65YAkAQ+q75b3WZteAZszeRgsQifTsJTb0JDsNQTSw@mail.gmail.com>
 <CAJpy0uCjAvaGn5y_48MR=DHahoB36VpqK6RkMryLKYBU+YHE5g@mail.gmail.com>
In-Reply-To: 
 <CAJpy0uCjAvaGn5y_48MR=DHahoB36VpqK6RkMryLKYBU+YHE5g@mail.gmail.com>
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Sun, 3 Aug 2025 21:41:24 +0530
Message-ID: 
 <CANhcyEXWkUbssy06k-uL8CxVquJ0qaUWxzAPJu0ynAzLY=FkSA@mail.gmail.com>
Subject: Re: Skipping schema changes in publication
To: shveta malik <shveta.malik@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
	"Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>,
 vignesh C <vignesh21@gmail.com>, YeXiu <1518981153@qq.com>,
	Ian Lawrence Barwick <barwick@gmail.com>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CANhcyEXWkUbssy06k-uL8CxVquJ0qaUWxzAPJu0ynAzLY%3DFkSA%40mail.gmail.com>
Precedence: bulk

On Tue, 22 Jul 2025 at 15:57, shveta malik <shveta.malik@gmail.com> wrote:
>
> Shlok, I was trying to validate the interaction of
> 'publish_via_partition_root' with 'EXCEPT". Found some unexpected
> behaviour, can you please review:
>
> Pub:
> ---------
> CREATE TABLE tab_root (range_col int,i int,j int) PARTITION BY RANGE
> (range_col);
> CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) to (1000);
> CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) to (2000);
> create publication pub2 for all tables except tab_part_2 WITH
> (publish_via_partition_root=true);
>
> Sub (tables without partition):
> --------
> CREATE TABLE tab_root (range_col int,i int,j int);
> CREATE TABLE tab_part_1(range_col int,i int,j int);
> CREATE TABLE tab_part_2(range_col int,i int,j int);
> create subscription sub2 connection '...' publication pub2;
>
> Pub:
> --------
> insert into tab_part_2 values(1001,1,1);
>
> On Sub, the above row is replicated as expected in tab_root due to
> publish_via_partition_root=true on pub.
>
> Now on Pub:
> --------
> alter publication pub2 set (publish_via_partition_root=false);
> insert into tab_part_2 values(1002,2,2);
>
> Now with publish_via_partition_root=false and 'except tab_part_2', the
> above row is correctly ignored and not replicated on sub.
>
> But when I try this:
> insert into tab_part_1 values(1,1,1);
> insert into tab_root values(5,5,5);
>
> Expectation was that the above rows are replicated but that is not the
> case. Can you please review? Please let me know if my understanding is
> wrong.

Hi Shveta,

I checked this behaviour on HEAD and found that it is the same
behaviour as HEAD. I think if we alter the parameter
'publish_via_partition_root', we should do ALTER SUBSCRIPTION ..
REFRESH PUBLICATION on subscriber.
I reviewed your behaviour and saw that after the 'alter publication
pub2 set (publish_via_partition_root=false)', the changes are still
being replicated to 'tab_root' on subscriber. And this behaviour is
similar to HEAD.

For example:
Pub:
---------
CREATE TABLE tab_root (range_col int,i int,j int) PARTITION BY RANGE
(range_col);
CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) to (1000);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) to (2000);
create publication pub2 for table tab_root WITH
(publish_via_partition_root=true);

Sub (tables without partition):
--------
CREATE TABLE tab_root (range_col int,i int,j int);
CREATE TABLE tab_part_1(range_col int,i int,j int);
CREATE TABLE tab_part_2(range_col int,i int,j int);
create subscription sub2 connection '...' publication pub2;

Pub:
--------
insert into tab_part_2 values(1001,1,1);

On Sub, the above row is replicated as expected in tab_root.

Now on Pub:
--------
alter publication pub2 set (publish_via_partition_root=false);

when I try this the data:
insert into tab_part_2 values(1002,2,2);
insert into tab_part_1 values(1,1,1);
insert into tab_root values(5,5,5);

The data is being replicated to tab_root on the subscriber.

After I do ALTER SUBSCRIPTION .. REFRESH PUBLICATION on subscriber,
replication happens as expected.

Also I found following documentation:
"Altering the <literal>publish_via_partition_root</literal> parameter can
lead to data loss or duplication at the subscriber because it changes
the identity and schema of the published tables. Note this happens only
when a partition root table is specified as the replication target."

Thanks,
Shlok Kyal