Approach1_v37_fail.txt

tab_root (RANGE range_col)
├── tab_part_1 (1–1000) PARTITION BY RANGE (i)
│   ├── tab_part_1_p1 (i 0–500)
│   └── tab_part_1_p2 (i 500–1000)
└── tab_part_2 (1000–2000) PARTITION BY RANGE (i)
    ├── tab_part_2_p1 (i 0–500)
    └── tab_part_2_p2 (i 500–1000)
	
Sceanrio 1:	
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) PARTITION BY RANGE (i);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) TO (2000) PARTITION BY RANGE (i);

-- Leaf partitions under tab_part_1
CREATE TABLE tab_part_1_p1 PARTITION OF tab_part_1 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_1_p2 PARTITION OF tab_part_1 FOR VALUES FROM (500) TO (1000);

-- Leaf partitions under tab_part_2
CREATE TABLE tab_part_2_p1 PARTITION OF tab_part_2 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_2_p2 PARTITION OF tab_part_2 FOR VALUES FROM (500) TO (1000);

-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500) 
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000) 
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500) 
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000) 
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);

CREATE PUBLICATION pub1 for all tables EXCEPT(tab_part_1,tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);

Sub:
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 TABLE tab_part_1_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_1_p2 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p2 (range_col int, i int, j int);
CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub1;

--Initial data belonging to tab_part_2_p1 alone should show up in table_root
select * from tab_root;

Pub:
--Now test incremental replication:
-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500) 
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000) 
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500) 
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000) 
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);

Sub:
-- Expectation: data belonging to tab_part_2_p1 alone should show up in tab_root.
-- Actual: tab_part_2_p2's data also show up  in tab_root
select * from tab_root;

postgres=# select * from tab_root;
 range_col |  i  | j  
-----------+-----+----
      1300 | 250 | 21
      1300 | 250 | 21
      1700 | 750 | 31 --> tab_part_2_p2 data
(3 rows)


~~
Sceanrio 2:
CREATE PUBLICATION pub2 for all tables EXCEPT(tab_root) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
--This gives error on Pub, expectation is no error:
CREATE TABLE tab_top_root (range_col int, i int, j int) PARTITION BY RANGE (range_col); 
ALTER TABLE tab_top_root ATTACH PARTITION tab_root FOR VALUES FROM (0) TO (2000);
ERROR:  cannot attach relation "tab_root" as partition because it is part of EXCEPT list in publication