RE: Added schema level support for publication. - Mailing list pgsql-hackers

On Monday, Tuesday, September 28, 2021 10:49 PM, vignesh C <vignesh21@gmail.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