AlterPubDropTable.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)
	
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);


CREATE PUBLICATION  pub1 for table tab_root;

postgres=# SELECT c.relname, p.pubname 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 
----------+---------
 tab_root | pub1
(1 row)

postgres=# select * from pg_publication_tables;
 pubname | schemaname |   tablename   |    attnames     | rowfilter 
---------+------------+---------------+-----------------+-----------
 pub1    | public     | tab_part_1_p1 | {range_col,i,j} | 
 pub1    | public     | tab_part_1_p2 | {range_col,i,j} | 
 pub1    | public     | tab_part_2_p1 | {range_col,i,j} | 
 pub1    | public     | tab_part_2_p2 | {range_col,i,j} | 
(4 rows)

postgres=# \dRp+
                                               Publication pub1
 Owner  | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root 
--------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
 shveta | f          | f             | t       | t       | t       | t         | none              | f
 
Tables:
    "public.tab_root"

--The partitions which were not part of pg_publication_rel, it did not consider it as included in publication
postgres=# alter publication pub1 drop table only tab_part_2;
ERROR:  relation "tab_part_2" is not part of the publication
postgres=# alter publication pub1 drop table only tab_part_1_p1;
ERROR:  relation "tab_part_1_p1" is not part of the publication

--It only let tab_root dropped. Even if I gave ONLY, all parititons were also dropped to be published.
postgres=# alter publication pub1 drop table only tab_root;
ALTER PUBLICATION

postgres=# SELECT c.relname, p.pubname 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 
---------+---------
(0 rows)

postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename | attnames | rowfilter 
---------+------------+-----------+----------+-----------
(0 rows)



=================================
drop publication pub1;	

--Now try with PUBLISH_VIA_PARTITION_ROOT as true:
create publication pub1 for table tab_root with ( PUBLISH_VIA_PARTITION_ROOT=true);
postgres=# \dRp+
                                               Publication pub1
 Owner  | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root 
--------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
 shveta | f          | f             | t       | t       | t       | t         | none              | t
Tables:
    "public.tab_root"
	
	
postgres=# SELECT c.relname, p.pubname 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 
----------+---------
 tab_root | pub1
(1 row)


--This output is different as compared to PUBLISH_VIA_PARTITION_ROOT=false case.
postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename |    attnames     | rowfilter 
---------+------------+-----------+-----------------+-----------
 pub1    | public     | tab_root  | {range_col,i,j} | 


--Rest of the output is same as that of PUBLISH_VIA_PARTITION_ROOT=false:
postgres=# alter publication pub1 drop table only tab_part_2;
ERROR:  relation "tab_part_2" is not part of the publication
postgres=#  alter publication pub1 drop table only tab_part_1_p1;
ERROR:  relation "tab_part_1_p1" is not part of the publication
postgres=# alter publication pub1 drop table only tab_part_1;
ERROR:  relation "tab_part_1" is not part of the publication
postgres=#  alter publication pub1 drop table only tab_root;
ALTER PUBLICATION
postgres=# SELECT c.relname, p.pubname 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 
---------+---------
(0 rows)

postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename | attnames | rowfilter 
---------+------------+-----------+----------+-----------
(0 rows)