Re: Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)

On Thu, Dec 24, 2020 at 11:02 AM Li Japin <japinli(at)hotmail(dot)com> wrote:
>
> Hi, hackers
>
> When I use logical stream replication on partition table, I find that if we create a new
> partitions after the subscription on subscriber, the records in new partitions cannot be
> shipped to the subscriber.
>
> Here is an example:
>
> 1. Create a view to check the subscription tables.
>
> ```
> — on subscriber
> CREATE VIEW pg_subscription_tables AS
> SELECT
> s.subname,
> n.nspname AS schemaname,
> c.relname AS tablename
> FROM
> pg_subscription s JOIN pg_subscription_rel p ON s.oid = p.srsubid,
> pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
> WHERE c.oid = p.srrelid;
> ```
>
> 1. Create a publication and subscription.
>
> ```
> — on publisher
> CREATE TABLE test_parent (a int, b int) PARTITION BY RANGE (a);
> CREATE TABLE test_child_01 PARTITION OF test_parent FOR VALUES FROM (1) TO (10);
> CREATE PUBLICATION my_test_pub FOR TABLE test_parent;
>
> — on subscriber
> CREATE TABLE test_parent (a int, b int) PARTITION BY RANGE (a);
> CREATE TABLE test_child_01 PARTITION OF test_parent FOR VALUES FROM (1) TO (10);
> CREATE SUBSCRIPTION my_test_sub CONNECTION 'host=localhost port=8765 dbname=postgres' PUBLICATION my_test_pub;
> ```
>
> 2. The insert data into test_parent on publisher, and everything looks good.
>
> ```
> — on publisher
> INSERT INTO test_parent VALUES (5, 50);
> SELECT * FROM pg_publication_tables;
> pubname | schemaname | tablename
> -------------+------------+---------------
> my_test_pub | public | test_child_01
> (1 row)
>
> — on subscriber
> SELECT * FROM test_parent;
> a | b
> ---+----
> 5 | 50
> (1 row)
>
> SELECT * FROM pg_subscription_tables;
> subname | schemaname | tablename
> -------------+------------+---------------
> my_test_sub | public | test_child_01
> (1 row)
> ```
>
> 3. However, If we create a new partitions on both publisher and subscriber. And the records
> in new partitions cannot ship to the subscriber. When I check the `pg_publication_tables`, I
> found that the new partitions are already in publication. But on the subscriber, the
> `pg_subscription_rel` do not have the new partitions.
>
> ```
> — on publisher
> CREATE TABLE test_child_02 PARTITION OF test_parent FOR VALUES FROM (10) TO (20);
> SELECT * FROM pg_publication_tables;
> pubname | schemaname | tablename
> -------------+------------+---------------
> my_test_pub | public | test_child_01
> my_test_pub | public | test_child_02
> (2 rows)
> INSERT INTO test_parent VALUES (15, 150);
>
> — on subscriber
> CREATE TABLE test_child_02 PARTITION OF test_parent FOR VALUES FROM (10) TO (20);
> SELECT * FROM test_parent;
> a | b
> ---+----
> 5 | 50
> (1 row)
>
> SELECT * FROM pg_subscription_tables;
> subname | schemaname | tablename
> -------------+------------+---------------
> my_test_sub | public | test_child_01
> (1 row)
> ```
>
> I think it looks strange.
>

The current behavior of partitioned tables is the same as for regular
tables. We don't automatically replicate the newly added tables to the
existing publication. So, if you try Alter Subscription my_test_sub
Refresh Publication;, it will replicate the newly added partition.

--
With Regards,
Amit Kapila.