On Fri, Jun 25, 2021 at 9:20 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> But I recently learned that when there are partitions in the
> publication, then toggling the value of the PUBLICATION option
> "publish_via_partition_root" [3] can also *implicitly* change the list
> published tables, and therefore that too might cause any ASRP to make
> use of the copy_data value for those implicitly added
> partitions/tables.
>
I have tried the below example in this context but didn't see any
effect on changing via_root option.
Set up on both publisher and subscriber:
=================================
CREATE TABLE tab2 (a int PRIMARY KEY, b text) PARTITION BY LIST (a);
CREATE TABLE tab2_1 (b text, a int NOT NULL);
ALTER TABLE tab2 ATTACH PARTITION tab2_1 FOR VALUES IN (0, 1, 2, 3);
CREATE TABLE tab2_2 PARTITION OF tab2 FOR VALUES IN (5, 6);
Publisher:
==========
CREATE PUBLICATION pub_viaroot FOR TABLE tab2_2;
postgres=# INSERT INTO tab2 VALUES (1), (0), (3), (5);
INSERT 0 4
postgres=# select * from tab2_1;
b | a
---+---
| 1
| 0
| 3
(3 rows)
postgres=# select * from tab2_2;
a | b
---+---
5 |
(1 row)
Subscriber:
==========
CREATE SUBSCRIPTION sub_viaroot CONNECTION 'host=localhost port=5432
dbname=postgres' PUBLICATION pub_viaroot;
postgres=# select * from tab2_2;
a | b
---+---
5 |
(1 row)
postgres=# select * from tab2_1;
b | a
---+---
(0 rows)
So, by this step, we can see the partition which is not subscribed is
not copied. Now, let's toggle via_root option.
Publisher
=========
Alter Publication pub_viaroot Set (publish_via_partition_root = true);
Subscriber
==========
postgres=# Alter Subscription sub_viaroot Refresh Publication;
ALTER SUBSCRIPTION
postgres=# select * from tab2_2;
a | b
---+---
5 |
(1 row)
postgres=# select * from tab2_1;
b | a
---+---
(0 rows)
As per your explanation, one can expect the data in tab2_1 in the last
step. Can you explain with example?
--
With Regards,
Amit Kapila.
