Approach1_challenges.txt

Consider the following complex partition hierarchy:
 
Plain Text
tab_root
├── tab_part_1
│   ├── tab_part_1_1
│   │   ├── tab_part_1_1_1
│   │   │   └── tab_part_1_1_1_1
│   │   └── tab_part_1_1_2
│   └── tab_part_1_2
│       ├── tab_part_1_2_1
│       └── tab_part_1_2_2
└── tab_part_2
 
Behavior on HEAD:
Assume a publication is created on the root table with publish_via_partition_root = true:
CREATE PUBLICATION pub1 FOR TABLE tab_root WITH (publish_via_partition_root = true);

In this case:
pg_publication_rel contains an entry only for tab_root.
pg_publication_tables also contains an entry only for tab_root.

When a subscription is created:
CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost port=5432 dbname=postgres' PUBLICATION pub1;
pg_subscription_rel is populated using pg_get_publication_tables, so it also contains only tab_root .
 
For each entry in pg_subscription_rel, a tablesync worker is launched.
For each tablesync worker, COPY command is constructed based on the corresponding info in pg_subscription_rel. For this case following copy command is constructed.
COPY tab_root TO STDOUT;
The data is then copied to the subscriber using COPY FROM.
 
-------------------------------------------------------------------------------------
Now, consider the behavior with the patch.

Suppose we create the following publication:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (tab_part_1_1) WITH (publish_via_partition_root = true);
Following Approach 1, we cannot have only a single entry for tab_root in pg_publication_tables, because tab_part_1_1 is explicitly excluded and its and its partitions' data must not be replicated.

Instead, pg_publication_tables should contain entries corresponding to the remaining relevant tables, such as:
tab_part_1_2
tab_part_2
(The exact set of tables still needs to be finalized.)

Next, pg_subscription_rel needs to be populated in a way that allows tablesync to:
1. Construct COPY commands only for tab_part_1_2 and tab_part_2 on the publisher
2. Apply the copied data into the root table tab_root  on the subscriber, since publish_via_partition_root is enabled

I think the main task for this scenario is to decide the what should be the content of pg_publication_tables.
And what should be populated in pg_subscription_rel to construct appropriate COPY query in tablesync worker.