Home > mailing lists

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

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

From

Li Japin

Date:

24 December 2020, 08:32:05

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?

[1] https://www.postgresql.org/docs/devel/sql-createpublication.html

Best regards

Japin Li

ChengDu WenWu Information Technology Co.Ltd.

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

From

Amit Kapila

Date:

24 December 2020, 09:20:24

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.