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

On Sat, 18 Jan 2025 at 14:29, Sergey Tatarintsev
<s(dot)tatarintsev(at)postgrespro(dot)ru> wrote:
>
> 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?

Yes, we should include the ancestors of the table to handle the
scenario you mentioned. The attached patch has the changes which
includes the ancestors also while getting the tables for the table
publication and schema publication. And in case of all tables
publication, get all the tables.
Thoughts?

Regards,
Vignesh