From 0746914d6deba48b1256dc196b1e98f9aed81cef Mon Sep 17 00:00:00 2001 From: wangw Date: Wed, 20 Apr 2022 16:57:31 +0800 Subject: [PATCH v3] Fix data replicated twice when specifying PUBLISH_VIA_PARTITION_ROOT option. If there are two publications that publish the parent table and the child table separately, and both specify the option PUBLISH_VIA_PARTITION_ROOT, when subscribing to both publications using one subscription, the data is replicated twice in inital copy. What we expect is to be copied only once. To fix this, we exclude the partitioned table whose ancestor belongs to specified publications when getting the table list from publisher. --- src/backend/commands/subscriptioncmds.c | 26 ++++++++++++++++++------ src/test/subscription/t/013_partition.pl | 20 +++++------------- 2 files changed, 25 insertions(+), 21 deletions(-) diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index b94236f74d..8f63dfe047 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -1759,20 +1759,34 @@ static List * fetch_table_list(WalReceiverConn *wrconn, List *publications) { WalRcvExecResult *res; - StringInfoData cmd; + StringInfoData cmd, + pub_names; TupleTableSlot *slot; Oid tableRow[2] = {TEXTOID, TEXTOID}; List *tablelist = NIL; + initStringInfo(&pub_names); + get_publications_str(publications, &pub_names, true); + + /* + * Get the list of tables from publisher, the partitioned table whose + * ancestor is also in this list should be ignored, otherwise the initial + * date in the partitioned table would be replicated twice. + */ initStringInfo(&cmd); - appendStringInfoString(&cmd, "SELECT DISTINCT t.schemaname, t.tablename\n" - " FROM pg_catalog.pg_publication_tables t\n" - " WHERE t.pubname IN ("); - get_publications_str(publications, &cmd, true); - appendStringInfoChar(&cmd, ')'); + appendStringInfo(&cmd, "SELECT DISTINCT ns.nspname, c.relname\n" + " FROM pg_catalog.pg_publication_tables t\n" + " JOIN pg_catalog.pg_namespace ns ON ns.nspname = t.schemaname\n" + " JOIN pg_catalog.pg_class c ON c.relname = t.tablename AND c.relnamespace = ns.oid\n" + " WHERE t.pubname IN (%s)\n" + " AND (c.relispartition IS FALSE OR NOT EXISTS (SELECT 1 FROM pg_partition_ancestors(c.oid)\n" + " WHERE relid IN ( SELECT DISTINCT (schemaname || '.' || tablename)::regclass::oid\n" + " FROM pg_catalog.pg_publication_tables t\n" + " WHERE t.pubname IN (%s) ) AND relid != c.oid))", pub_names.data, pub_names.data); res = walrcv_exec(wrconn, cmd.data, 2, tableRow); pfree(cmd.data); + pfree(pub_names.data); if (res->status != WALRCV_OK_TUPLES) ereport(ERROR, diff --git a/src/test/subscription/t/013_partition.pl b/src/test/subscription/t/013_partition.pl index 66e63e755e..6313d425dc 100644 --- a/src/test/subscription/t/013_partition.pl +++ b/src/test/subscription/t/013_partition.pl @@ -476,14 +476,14 @@ $node_subscriber2->safe_psql('postgres', "CREATE TABLE tab3_1 (a int PRIMARY KEY, c text DEFAULT 'sub2_tab3_1', b text)" ); -# Note: We create two separate tables, not a partitioned one, so that we can -# easily identity through which relation were the changes replicated. +# Note: We only create one table for the partition table (tab4) here. +# Because we specify option PUBLISH_VIA_PARTITION_ROOT (see pub_all and +# pub_lower_level above), all data should be replicated to the partition table. +# So we do not need to create table for the partitioned table. $node_subscriber2->safe_psql('postgres', "CREATE TABLE tab4 (a int PRIMARY KEY)" ); -$node_subscriber2->safe_psql('postgres', - "CREATE TABLE tab4_1 (a int PRIMARY KEY)" -); + # Publication that sub2 points to now publishes via root, so must update # subscription target relations. We set the list of publications so that # the FOR ALL TABLES publication is second (the list order matters). @@ -559,11 +559,6 @@ $result = $node_subscriber2->safe_psql('postgres', "SELECT a FROM tab4 ORDER BY 1"); is( $result, qq(0), 'inserts into tab4 replicated'); -$result = $node_subscriber2->safe_psql('postgres', - "SELECT a FROM tab4_1 ORDER BY 1"); -is( $result, qq(), 'inserts into tab4_1 replicated'); - - # now switch the order of publications in the list, try again, the result # should be the same (no dependence on order of pulications) $node_subscriber2->safe_psql('postgres', @@ -588,11 +583,6 @@ $result = $node_subscriber2->safe_psql('postgres', is( $result, qq(0 1), 'inserts into tab4 replicated'); -$result = $node_subscriber2->safe_psql('postgres', - "SELECT a FROM tab4_1 ORDER BY 1"); -is( $result, qq(), 'inserts into tab4_1 replicated'); - - # update (replicated as update) $node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 6 WHERE a = 5"); $node_publisher->safe_psql('postgres', "UPDATE tab2 SET a = 6 WHERE a = 5"); -- 2.23.0.windows.1