Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)
| От | Li Japin |
|---|---|
| Тема | Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false) |
| Дата | |
| Msg-id | 1D6DCFD2-0F44-4A18-BF67-17C2697B1631@hotmail.com обсуждение исходный текст |
| Ответы |
Re: Cannot ship records to subscriber for partition tables using logical replication (publish_via_partition_root=false)
(Amit Kapila <amit.kapila16@gmail.com>)
|
| Список | 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 по дате отправления: