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.
