Re: Added schema level support for publication.

On Wed, Oct 13, 2021 at 12:15 AM vignesh C <vignesh21@gmail.com> wrote:
>
> Attached v40 patch has the fix for the above comments.
>

[Maybe this has some overlap with what Hou-san reported, and I have
not tested this against his proposed fixes]

If partitions belong to a different schema than the parent partitioned
table, then the current patch implementation allows the partitions to
(optionally) be explicitly added to a publication that includes the
parent partitioned table (and for the most part, it doesn't seem to
make any difference to the publication behavior). Should this be
allowed?

e.g.

CREATE SCHEMA sch;
CREATE SCHEMA sch1;
CREATE TABLE sch.sale (sale_date date not null, country_code text,
product_sku text, units integer) PARTITION BY RANGE (sale_date);
CREATE TABLE sch1.sale_201901 PARTITION OF sch.sale FOR VALUES FROM
('2019-01-01') TO ('2019-02-01');
CREATE TABLE sch1.sale_201902 PARTITION OF sch.sale FOR VALUES FROM
('2019-02-01') TO ('2019-03-01');

postgres=# CREATE PUBLICATION pub FOR ALL TABLES IN SCHEMA sch, TABLE
sch1.sale_201901, TABLE sch1.sale_201902;
CREATE PUBLICATION
postgres=# \dRp+
                             Publication pub
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 gregn | f          | t       | t       | t       | t         | f
Tables:
    "sch1.sale_201901"
    "sch1.sale_201902"
Tables from schemas:
    "sch"


Also, I found the following scenario where the data is double-published:

(1) PUB:  CREATE PUBLICATION pub FOR TABLE sch1.sale_201901, TABLE
sch1.sale_201902 WITH (publish_via_partition_root=true);
(2) SUB:  CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres
host=localhost port=5432' PUBLICATION pub;
(3) PUB:  INSERT INTO sch.sale VALUES('2019-01-01', 'AU', 'cpu', 5),
('2019-01-02', 'AU', 'disk', 8);
(4) SUB:  SELECT * FROM sch.sale;
(5) PUB:  ALTER PUBLICATION pub ADD ALL TABLES IN SCHEMA sch;
(6) SUB:  ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
(7) SUB:  SELECT * FROM sch.sale;

sale_date  | country_code | product_sku | units
------------+--------------+-------------+-------
 2019-01-01 | AU           | cpu         |     5
 2019-01-02 | AU           | disk        |     8
 2019-01-01 | AU           | cpu         |     5
 2019-01-02 | AU           | disk        |     8


Regards,
Greg Nancarrow
Fujitsu Australia