Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false) - Mailing list pgsql-hackers

From Li Japin
Subject Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)
Date
Msg-id 1D6DCFD2-0F44-4A18-BF67-17C2697B1631@hotmail.com
Whole thread Raw
Responses Re: Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)
List pgsql-hackers
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.


pgsql-hackers by date:

Previous
From: vignesh C
Date:
Subject: Re: Parallel Inserts in CREATE TABLE AS
Next
From: Amit Kapila
Date:
Subject: Re: [HACKERS] logical decoding of two-phase transactions