Re: Skipping schema changes in publication - Mailing list pgsql-hackers

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