RE: Added schema level support for publication.

On Monday, Tuesday, September 28, 2021 10:49 PM, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> Yes this should not be supported, we should throw an error in this case.
> This is handled in the v34 patch attached at [1].
> [1] - https://www.postgresql.org/message-
> id/CALDaNm2Z9TfuoCf09YGKfwy7F1NwC4iCXJGTaZS%3DchH6VHtadQ%40mail.g
> mail.com
>

Thanks for fixing it. I confirmed the error can be output as expected.

Here is a problem related to publish_via_partition_root option when using this
patch. With this option on, I think pg_get_publication_tables function gave an
unexcepted result and the subscriber would get dual data during table sync.

For example:
(I used pg_publication_tables view to make it looks clearer)

create schema sch1;
create table sch1.tbl1 (a int) partition by range ( a );
create table sch1.tbl1_part1 partition of sch1.tbl1 for values from (1) to (10);
create table sch1.tbl1_part2 partition of sch1.tbl1 for values from (10) to (20);
create table sch1.tbl1_part3 partition of sch1.tbl1 for values from (20) to (30);
create publication pub for all tables in schema sch1 with(publish_via_partition_root=1);

postgres=# select * from pg_publication_tables where pubname='pub';
pubname | schemaname | tablename
---------+------------+------------
pub | sch1 | tbl1_part1
pub | sch1 | tbl1_part2
pub | sch1 | tbl1_part3
pub | sch1 | tbl1
(4 rows)

It shows both the partitioned table and its leaf partitions. But the result of
FOR ALL TABLES publication couldn't show the leaf partitions.

postgres=# create publication pub_all for all tables with(publish_via_partition_root=1);
CREATE PUBLICATION
postgres=# select * from pg_publication_tables where pubname='pub_all';
pubname | schemaname | tablename
---------+------------+-----------
pub_all | sch1 | tbl1
(1 row)

How about make the following change to avoid it? I tried it and it also fixed dual
data issue during table sync.

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 04e785b192..4e8ccdabc6 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -632,7 +632,8 @@ GetSchemaPublicationRelations(Oid schemaid, PublicationPartOpt pub_partopt)
Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple);
Oid relid = relForm->oid;

- if (is_publishable_class(relid, relForm))
+ if (is_publishable_class(relid, relForm) &&
+ !(relForm->relispartition && pub_partopt == PUBLICATION_PART_ROOT))
result = lappend_oid(result, relid);
}

Regards
Tang