Re: Added schema level support for publication.

On Thu, Oct 14, 2021 at 9:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> > 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"
> >
>
> I don't see any problem with this. Do you have a specific problem in
> mind due to this?
>

I'm not sure if it's a problem as such, really just a query from me as
to whether it should be allowed to also (redundantly) add partitions
to the publication, in addition to the partitioned table, since the
current documentation says: "When a partitioned table is added to a
publication, all of its existing and future partitions are implicitly
considered to be part of the publication".
I guess it should be allowed, as I find I can do it in the current
implementation just with TABLE.

Regards,
Greg Nancarrow
Fujitsu Australia