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

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. But if we create publication with `publish_via_partition_root` it work fine,
since all records are ship on the partitioned table [1].

When `publish_via_partition_root` is false, since the publisher add the new partitions in
publication,  should we add them on the subscriber automatically?



--
Best regards
Japin Li
ChengDu WenWu Information Technology Co.Ltd.


On Thu, Dec 24, 2020 at 11:02 AM Li Japin <japinli@hotmail.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.