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

From: Li Japin <japinli(at)hotmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)
Date: 2020-12-24 05:32:05
Message-ID: 1D6DCFD2-0F44-4A18-BF67-17C2697B1631@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

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

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2020-12-24 05:34:58 Re: [HACKERS] logical decoding of two-phase transactions
Previous Message vignesh C 2020-12-24 04:55:06 Re: Parallel Inserts in CREATE TABLE AS