RowFilterBehaviour.txt

Row filter behaviour on HEAD
========================
 
OBSERVATION:
When publish_via_partition_root is true, a row filter can be created for both the partitioned table and its partitions. The filter defined on the partitioned table is applied to all partitions, while a filter defined on a specific partition applies only to that partition.
When publish_via_partition_root is false, creating a publication with a row filter on the partitioned table itself fails. Row filters can still be created for individual partitions, but they are applied only to the corresponding partitions.

TEST for better understanding
---------------
 
CREATE TABLE parent (id int primary key, val int) PARTITION BY RANGE (id);
CREATE TABLE child1 PARTITION OF parent FOR VALUES FROM (0) TO (100);
CREATE TABLE child2 PARTITION OF parent FOR VALUES FROM (100) TO (200);
 
<publish_via_partition_root =true>
Test A: Root-level filter
--------------------
CREATE PUBLICATION pub_root FOR TABLE parent WHERE (val > 10) WITH (publish_via_partition_root = true);
 
CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub_root;
 
INSERT INTO parent VALUES (50, 5);   -- not published
INSERT INTO parent VALUES (60, 20);  -- published
 
INSERT INTO child1 VALUES (70, 5);  -- not published
INSERT INTO child1 VALUES (80, 20);  -- published
 
INSERT INTO child2 VALUES (150, 5);   -- not published
INSERT INTO child2 VALUES (160, 20); -- published
 
Conclusion: When publish_via_partition_root=true, the row filter defined on the root table is applied to all partitions.
 
Test B: Partition-level filter 
--------------------
drop publication pub_root;
drop subscription sub1;
truncate parent;
 
CREATE PUBLICATION pub_part FOR TABLE child1 WHERE (val > 10) WITH (publish_via_partition_root = true);
create subscription sub2 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub_part;
 
INSERT INTO parent VALUES (50, 5);   -- not published
INSERT INTO parent VALUES (60, 20);  -- published
 
INSERT INTO child1 VALUES (70, 5);  -- not published
INSERT INTO child1 VALUES (80, 20);  -- published
 
 
INSERT INTO child2 VALUES (150, 5);   -- not published (as publication is only for child1)
INSERT INTO child2 VALUES (160, 20);  -- not published (as publication is only for child1)
 
Conclusion: When publish_via_partition_root=true, the row filter defined on the partition is applied to that parition.
 
------------
Now test above publish_via_partition_root as false
------------
drop publication pub_part;
drop subscription sub2;
truncate parent;
 
 
Test A: Root-level filter
--------------------
CREATE PUBLICATION pub_root FOR TABLE parent WHERE (val > 10) WITH (publish_via_partition_root = false);
create subscription sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub_root;
 
postgres=# CREATE PUBLICATION pub_root FOR TABLE parent WHERE (val > 10) WITH (publish_via_partition_root = false);
ERROR:  cannot use publication WHERE clause for relation "parent"
DETAIL:  WHERE clause cannot be used for a partitioned table when publish_via_partition_root is false.
 
 
Test B: Partition-level filter 
-----------------------
CREATE PUBLICATION pub_part FOR TABLE child1 WHERE (val > 10) WITH (publish_via_partition_root = false);
create subscription sub2 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub_part;
INSERT INTO parent VALUES (70, 5);   -- not published
INSERT INTO parent VALUES (80, 20);  -- published
INSERT INTO child1 VALUES (85, 5);  -- not published
INSERT INTO child1 VALUES (90, 20);  -- published
 
INSERT INTO parent VALUES (180, 20); -- not published (as publication is only for child1)

Conclusion: When publish_via_partition_root=false, the row filter defined on the partition is applied to that parition.