Re: create subscription with (origin = none, copy_data = on)

On Fri, 17 Jan 2025 at 21:30, vignesh C <vignesh21@gmail.com> wrote:

On Fri, 17 Jan 2025 at 14:00, Sergey Tatarintsev
<s.tatarintsev@postgrespro.ru> wrote:

Hi, hackers!

I am looking at subscription creation command:

CREATE SUBSCRIPTION sub CONNECTION '...' PUBLICATION pub WITH (origin =
none, copy_data = on);

For now we log a warning if the publisher has subscribed to the same
table from some other publisher.
However, in case of publication with publish_via_partition_root option,
we will not raise such warinigs
because SQL command in check_publications_origin() checks only directly
published tables.

Yes, I agree that we are checking only the directly published tables
which is why there is no warning in this case. I'm working on a fix to
change the check_publications_origin to check accordingly.

Attached patch has the fix for this issue which includes the partition
tables also for the publication now and throws a warning
appropriately.

Regards,
Vignesh

Thanks for patch!

I think we must take into account whole inheritance tree of partitioned table.

For example:

node_A:
CREATE TABLE t(id int);
CREATE PUBLICATION pub_b FOR TABLE t;

node_A:
CREATE TABLE t(id int) PARTITION BY RANGE(id);
CREATE TABLE part PARTITION OF t FOR VALUES FROM (0) TO (10) PARTITION BY RANGE(id);
CREATE TABLE subpart PARTITION OF part FOR VALUES FROM (0) TO (5);
CREATE SUBSCRIPTION sub_c CONNECTION '$node_B_connstr' PUBLICATION pub_b;
CREATE PUBLICATION pub_t FOR TABLE t WITH (publish_via_partition_root);
CREATE PUBLICATION pub_part FOR TABLE part WITH (publish_via_partition_root);

node_C:
-- this command will raise a warning CREATE SUBSCRIPTION sub_t CONNECTION '$node_A_connstr' PUBLICATION pub_t WITH (origin = none, copy_data = on);
DROP SUBSCRIPTION IF EXISTS sub_t;
-- here we got silence, but "part" is in tree of upper level replicated table
CREATE SUBSCRIPTION sub_part CONNECTION '$node_A_connstr' PUBLICATION pub_part WITH (origin = none, copy_data = on);
DROP SUBSCRIPTION IF EXISTS sub_part;

I think that for each partition/partitioned table in the publication we can use something like

select relid from pg_partition_tree('part'::regclass)
union
select relid from pg_partition_ancestors('part'::regclass);

In this case  we don't care about  publish_via_partition_root option, because we already check all inheritance tree, and there is no need to change pg_class

What are you thinking about it?