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

18.01.2025 12:01, vignesh C пишет:
> 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?