DifferentPubViaRoot.txt

tab_root  (RANGE partitioned on range_col)
├── tab_part_1  (range_col: 1 → 1000)
│   ├── tab_part_1_p1  (range_col: 1 → 500)
│   └── tab_part_1_p2  (range_col: 500 → 1000)
└── tab_part_2  (range_col: 1000 → 2000)
    ├── tab_part_2_p1  (range_col: 1000 → 1500)
    └── tab_part_2_p2  (range_col: 1500 → 2000)

CREATE TABLE tab_root (id int, range_col int) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) TO (1000) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) TO (2000) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1_p1 PARTITION OF tab_part_1 FOR VALUES FROM (1) TO (500);
CREATE TABLE tab_part_1_p2 PARTITION OF tab_part_1 FOR VALUES FROM (500) TO (1000);
CREATE TABLE tab_part_2_p1 PARTITION OF tab_part_2 FOR VALUES FROM (1000) TO (1500);
CREATE TABLE tab_part_2_p2 PARTITION OF tab_part_2 FOR VALUES FROM (1500) TO (2000);


INSERT INTO tab_root (id, range_col) VALUES (1, 100);
INSERT INTO tab_root (id, range_col) VALUES (2, 600);
INSERT INTO tab_root (id, range_col) VALUES (3, 1100);
INSERT INTO tab_root (id, range_col) VALUES (4, 1600);

CREATE PUBLICATION pub1 for ALL TABLES EXCEPT table (tab_part_1_p1, tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
CREATE PUBLICATION pub2 for ALL TABLES EXCEPT table (tab_part_2) WITH (PUBLISH_VIA_PARTITION_ROOT=false);

postgres=# SELECT c.relname, p.pubname, pr.prexcept FROM pg_publication_rel pr JOIN pg_class c ON c.oid = pr.prrelid JOIN pg_publication p ON p.oid = pr.prpubid;
    relname    | pubname | prexcept 
---------------+---------+----------
 tab_part_1_p1 | pub1    | t
 tab_part_2    | pub2    | t
 tab_part_2_p2 | pub1    | t
(3 rows)



postgres=# select * from pg_publication_tables;                                                                                        
 pubname | schemaname |   tablename   |    attnames    | rowfilter 
---------+------------+---------------+----------------+-----------
 pub1    | public     | tab_root      | {id,range_col} | 
 pub2    | public     | tab_part_1_p1 | {id,range_col} | 
 pub2    | public     | tab_part_1_p2 | {id,range_col} | 
(3 rows)


Sub:
CREATE TABLE tab_root (id int, range_col int);
CREATE TABLE tab_part_1 (id int, range_col int);
CREATE TABLE tab_part_2 (id int, range_col int);
CREATE TABLE tab_part_1_p1 (id int, range_col int);
CREATE TABLE tab_part_1_p2 (id int, range_col int);
CREATE TABLE tab_part_2_p1 (id int, range_col int);
CREATE TABLE tab_part_2_p2 (id int, range_col int);

CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub1, pub2;
postgres=# SELECT s.subname, c.relname, r.srsubstate, r.srsublsn FROM pg_subscription_rel r JOIN pg_class c ON r.srrelid = c.oid JOIN pg_subscription s ON r.srsubid = s.oid;
 subname | relname  | srsubstate |  srsublsn  
---------+----------+------------+------------
 sub1    | tab_root | r          | 0/017958A8
(1 row)


From Log:
Tablesync worker: Executing query to get the initial sync data:
	COPY (
	SELECT id, range_col FROM  public.tab_part_2_p1
	UNION ALL
	SELECT id, range_col FROM  public.tab_part_1
	)
	TO STDOUT
	
Output of table-sync:
postgres=# select * from tab_root;
 id | range_col 
----+-----------
  1 |       100  --tab_part_1_p1
  2 |       600  --tab_part_1_p2
  3 |      1100  --tab_part_2_p1
(3 rows)

Pub (to test incremental sync):
INSERT INTO tab_root (id, range_col) VALUES (1, 200);   --missed tab_part_1_p1 data.
INSERT INTO tab_root (id, range_col) VALUES (2, 700);   --replicated tab_part_1_p2
INSERT INTO tab_root (id, range_col) VALUES (3, 1200);  --replicated tab_part_2_p1
INSERT INTO tab_root (id, range_col) VALUES (4, 1700);
 
Sub: Result of incremental sync alone:
postgres=# select * from tab_root order by range_col; 
id | range_col 
----+----------- 
  2 |       700 
  3 |      1200 

postgres=# select * from tab_part_1_p1;
 id | range_col 
----+-----------
(0 rows)

postgres=# select * from tab_part_1_p2;
 id | range_col 
----+-----------
(0 rows)

postgres=# select * from tab_part_2_p1;
 id | range_col 
----+-----------
(0 rows)

postgres=# select * from tab_part_2_p2;
 id | range_col 
----+-----------
(0 rows)

So tablesync has replicated the table if it was included in any of the publication while incremental sync has missed publishing tab_part_1_p1 even though it was included in pub2.