public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Peter Smith <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: Greg Nancarrow <[email protected]>
Cc: vignesh C <[email protected]>
Subject: RE: Data is copied twice when specifying both child and parent table in publication
Date: Wed, 28 Sep 2022 08:35:33 +0000
Message-ID: <OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAHut+PtTN1Udug3x1eZnDdh8Z6PL5VN=xz1b44VbB9T1z_PtcQ@mail.gmail.com>
References: <OS3PR01MB62751414E4DABF144F5E481C9E529@OS3PR01MB6275.jpnprd01.prod.outlook.com>
<CAHut+PtTN1Udug3x1eZnDdh8Z6PL5VN=xz1b44VbB9T1z_PtcQ@mail.gmail.com>
On Tues, Sep 27, 2022 at 16:45 PM Peter Smith <[email protected]> wrote:
> Here are my review comments for the HEAD_v11-0001 patch:
Thanks for your comments.
> ======
>
> 1. General - Another related bug?
>
> In [1] Hou-san wrote:
>
> For another case you mentioned (via_root used when publishing child)
> CREATE PUBLICATION pub1 for TABLE parent;
> CREATE PUBLICATION pub2 for TABLE child with (publish_via_partition_root);
> CREATE SUBSCRIPTION sub connect xxx PUBLICATION pub1,pub2;
>
> The expected behavior is only the child table is published, all the changes
> should be replicated using the child table's identity. We should do table sync
> only for child tables and is same as the current behavior on HEAD. So, I think
> there is no bug in this case.
>
> ~
>
> That behaviour seems different to my understanding because the pgdocs
> says when the via_root param is true the 'child' table would be using
> the 'parent' identity:
>
> [2] publish_via_partition_root determines whether changes in a
> partitioned table (or on its partitions) contained in the publication
> will be published using the identity and schema of the partitioned
> table rather than that of the individual partitions that are actually
> changed.
>
> ~
>
> So is this another bug (slightly different from the current one being
> patched), or is it just some different special behaviour? If it's
> another bug then you need to know that ASAP because I think you may
> want to fix both of them at the same time which might impact how this
> 2x data copy patch should be implemented.
>
> Or perhaps just the pgdocs need more notes about special
> cases/combinations like this?
>
> ======
>
> 2. General - documentation?
>
> For this current patch, IIUC it was decided that it is a bug because
> the data gets duplicated, and then some sensible rule was decided that
> this patch should use to address it (e.g. publishing a child combined
> with publishing a parent via_root will just ignore the child's
> publication...).
>
> So my question is - is this (new/fixed) behaviour something that a
> user will be able to figure out themselves from the existing
> documentation, or does this patch now need its own special notes in
> the documentation?
IMO this behaviour doesn't look like a bug.
I think the behaviour of multiple publications with parameter
publish_via_partition_root could be added to the pg-doc later in a separate
patch.
> ======
>
> 3. src/backend/catalog/pg_publication.c - pg_get_publication_tables
>
> + foreach(lc, pub_elem_tables)
> + {
> + Oid *result = (Oid *) malloc(sizeof(Oid) * 2);
> +
> + result[0] = lfirst_oid(lc);
> + result[1] = pub_elem->oid;
> + table_infos = lappend(table_infos, result);
> + }
>
> 3a.
> It looks like each element in the table_infos list is a malloced obj
> of 2x Oids (Oid of table, Oid of pub). IMO better to call this element
> 'table_info' instead of the meaningless 'result'
>
> ~
>
> 3b.
> Actually, I think it would be better if this function defines a little
> 2-element structure {Oid relid, Oid pubid} to use instead of this
> array (which requires knowledge that [0] means relid and [1] means
> pubid).
>
> ~~~
>
> 4.
>
> + foreach(lc, table_infos)
> + {
> + Oid *table_info_tmp = (Oid *) lfirst(lc);
> +
> + if (!list_member_oid(tables, table_info_tmp[0]))
> + table_infos = foreach_delete_current(table_infos, lc);
> }
> I think the '_tmp' suffix is not helpful here - IMO having another
> relid variable would make this more self-explanatory.
>
> Or better yet adopt the suggestion o f #3b and have a little struct
> with self-explanatory member names.
Improved as suggested.
> =====
>
> 5. src/backend/commands/subscriptioncmds.c - fetch_table_list
>
> + if (server_version >= 160000)
> + appendStringInfo(&cmd, "SELECT DISTINCT N.nspname, C.relname,\n"
>
> Since there is an else statement block, I think this would be more
> readable if there was a statement block here too. YMMV
>
> SUGGESTION
> if (server_version >= 160000)
> {
> appendStringInfo(&cmd, "SELECT DISTINCT N.nspname, C.relname,\n"
> ...
> }
Improved as suggested.
> ~~~
>
> 6.
>
> + /*
> + * Get the list of tables from publisher, the partition table whose
> + * ancestor is also in this list will be ignored, otherwise the initial
> + * data in the partition table would be replicated twice.
> + */
>
> 6a.
> "from publisher, the partition" -> "from the publisher. The partition"
>
> ~
>
> 6b.
> This looks like a common comment that also applied to the "if" part,
> so it seems more appropriate to move it to where I indicated below.
> Perhaps the whole comment needs a bit of massaging after you move
> it...
>
> + /*
> + * Get namespace, relname and column list (if supported) of the tables
> + * belonging to the specified publications.
> + *
> + * HERE <<<<<<<<<
> + *
> + * From version 16, the parameter of the function pg_get_publication_tables
> + * can be an array of publications. The partition table whose ancestor is
> + * also published in this publication array will be filtered out in this
> + * function.
> + */
Improved as suggested.
Also rebased the patch because the change in the HEAD (20b6847).
Attach the new patches.
Regards,
Wang wei
Attachments:
[application/octet-stream] HEAD_v12-0001-Fix-data-replicated-twice-when-specifying-publis.patch (19.7K, ../OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com/2-HEAD_v12-0001-Fix-data-replicated-twice-when-specifying-publis.patch)
download | inline diff:
From 6af8bede48777b3acd9ed3c5d63bbc4b6f79f094 Mon Sep 17 00:00:00 2001
From: wangw <[email protected]>
Date: Tue, 14 Jun 2022 13:44:09 +0800
Subject: [PATCH v12] Fix data replicated twice when specifying
publish_via_partition_root option.
If there are two publications - one of them publishing a parent table
(using publish_via_partition_root = true) and the other is publishing one of
the parent's child tables - then subscribing to both publications from one
subscription results in the same initial child data being copied twice. It
should only be copied once.
To fix this, the API function pg_get_publication_tables has been extended to
take a publication list. Now, when getting the table information, if the
publish_via_partition_root is true, the function can exclude a partition table
whose ancestor is also published by the same publication list.
---
src/backend/catalog/pg_publication.c | 151 +++++++++++++++------
src/backend/commands/subscriptioncmds.c | 81 +++++++++--
src/include/catalog/pg_proc.dat | 12 +-
src/test/regress/expected/rules.out | 2 +-
src/test/subscription/t/013_partition.pl | 18 +--
src/test/subscription/t/028_row_filter.pl | 13 +-
src/test/subscription/t/031_column_list.pl | 5 +-
7 files changed, 205 insertions(+), 77 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 59967098b3..830f2b7537 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1026,22 +1026,35 @@ GetPublicationByName(const char *pubname, bool missing_ok)
}
/*
- * Returns information of tables in a publication.
+ * Get information of the tables in the given publication array.
+ *
+ * Returns the oid, column list, row filter for each table.
*/
Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
#define NUM_PUBLICATION_TABLES_ELEM 3
+ typedef struct
+ {
+ Oid relid; /* OID of published table */
+ Oid pubid; /* OID of publication that publishes this
+ * table. */
+ } published_rel;
FuncCallContext *funcctx;
- char *pubname = text_to_cstring(PG_GETARG_TEXT_PP(0));
- Publication *publication;
- List *tables;
+ List *tables = NIL,
+ *table_infos = NIL;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
{
TupleDesc tupdesc;
MemoryContext oldcontext;
+ ArrayType *arr;
+ Datum *elems;
+ int nelems,
+ i;
+ bool viaroot = false;
+ ListCell *lc;
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
@@ -1049,42 +1062,94 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
/* switch to memory context appropriate for multiple function calls */
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
- publication = GetPublicationByName(pubname, false);
-
/*
- * Publications support partitioned tables, although all changes are
- * replicated using leaf partition identity and schema, so we only
- * need those.
+ * Deconstruct the parameter into elements where each element is a
+ * publication name.
*/
- if (publication->alltables)
- {
- tables = GetAllTablesPublicationRelations(publication->pubviaroot);
- }
- else
+ arr = PG_GETARG_ARRAYTYPE_P(0);
+ deconstruct_array(arr, TEXTOID, -1, false, TYPALIGN_INT,
+ &elems, NULL, &nelems);
+
+ /* Get Oids of tables from each publication. */
+ for (i = 0; i < nelems; i++)
{
- List *relids,
- *schemarelids;
-
- relids = GetPublicationRelations(publication->oid,
- publication->pubviaroot ?
- PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
- schemarelids = GetAllSchemaPublicationRelations(publication->oid,
- publication->pubviaroot ?
- PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
- tables = list_concat_unique_oid(relids, schemarelids);
+ Publication *pub_elem;
+ List *pub_elem_tables = NIL;
+
+ pub_elem = GetPublicationByName(TextDatumGetCString(elems[i]), false);
/*
- * If the publication publishes partition changes via their
- * respective root partitioned tables, we must exclude partitions
- * in favor of including the root partitioned tables. Otherwise,
- * the function could return both the child and parent tables
- * which could cause data of the child table to be
- * double-published on the subscriber side.
+ * Publications support partitioned tables. If
+ * publish_via_partition_root is false, all changes are replicated
+ * using leaf partition identity and schema, so we only need
+ * those. Otherwise, If publish_via_partition_root is true, get
+ * the partitioned table itself.
*/
- if (publication->pubviaroot)
- tables = filter_partitions(tables);
+ if (pub_elem->alltables)
+ pub_elem_tables = GetAllTablesPublicationRelations(pub_elem->pubviaroot);
+ else
+ {
+ List *relids,
+ *schemarelids;
+
+ relids = GetPublicationRelations(pub_elem->oid,
+ pub_elem->pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
+ schemarelids = GetAllSchemaPublicationRelations(pub_elem->oid,
+ pub_elem->pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
+ pub_elem_tables = list_concat(relids, schemarelids);
+ }
+
+ /*
+ * Record the published table and the corresponding publication so
+ * that we can get row filters and column list later.
+ */
+ foreach(lc, pub_elem_tables)
+ {
+ published_rel *table_info = (published_rel *) malloc(sizeof(published_rel));
+
+ table_info->relid = lfirst_oid(lc);
+ table_info->pubid = pub_elem->oid;
+ table_infos = lappend(table_infos, table_info);
+ }
+
+ tables = list_concat(tables, pub_elem_tables);
+
+ /* At least one publication is using publish_via_partition_root. */
+ if (pub_elem->pubviaroot)
+ viaroot = true;
+ }
+
+ pfree(elems);
+
+ /* Now sort and de-duplicate the tables list */
+ list_sort(tables, list_oid_cmp);
+ list_deduplicate_oid(tables);
+
+ /*
+ * If the publication publishes partition changes via their respective
+ * root partitioned tables, we must exclude partitions in favor of
+ * including the root partitioned tables. Otherwise, the function
+ * could return both the child and parent tables which could cause
+ * data of the child table to be double-published on the subscriber
+ * side.
+ */
+ if (viaroot)
+ tables = filter_partitions(tables);
+
+ /*
+ * For tables that have been filtered out, delete the corresponding
+ * table information in the table_infos list.
+ */
+ foreach(lc, table_infos)
+ {
+ published_rel *table_info = (published_rel *) lfirst(lc);
+
+ if (!list_member_oid(tables, table_info->relid))
+ table_infos = foreach_delete_current(table_infos, lc);
}
/* Construct a tuple descriptor for the result rows. */
@@ -1097,20 +1162,22 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
PG_NODE_TREEOID, -1, 0);
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
- funcctx->user_fctx = (void *) tables;
+ funcctx->user_fctx = (void *) table_infos;
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
- tables = (List *) funcctx->user_fctx;
+ table_infos = (List *) funcctx->user_fctx;
- if (funcctx->call_cntr < list_length(tables))
+ if (funcctx->call_cntr < list_length(table_infos))
{
HeapTuple pubtuple = NULL;
HeapTuple rettuple;
- Oid relid = list_nth_oid(tables, funcctx->call_cntr);
+ Publication *pub;
+ published_rel *table_info = (published_rel *) list_nth(table_infos, funcctx->call_cntr);
+ Oid relid = table_info->relid;
Oid schemaid = get_rel_namespace(relid);
Datum values[NUM_PUBLICATION_TABLES_ELEM] = {0};
bool nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
@@ -1119,7 +1186,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
* Form tuple with appropriate data.
*/
- publication = GetPublicationByName(pubname, false);
+ pub = GetPublication(table_info->pubid);
values[0] = ObjectIdGetDatum(relid);
@@ -1127,13 +1194,13 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
* We don't consider row filters or column lists for FOR ALL TABLES or
* FOR TABLES IN SCHEMA publications.
*/
- if (!publication->alltables &&
+ if (!pub->alltables &&
!SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
ObjectIdGetDatum(schemaid),
- ObjectIdGetDatum(publication->oid)))
+ ObjectIdGetDatum(pub->oid)))
pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP,
ObjectIdGetDatum(relid),
- ObjectIdGetDatum(publication->oid));
+ ObjectIdGetDatum(pub->oid));
if (HeapTupleIsValid(pubtuple))
{
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f3bfcca434..c7630872ab 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -1915,25 +1915,84 @@ static List *
fetch_table_list(WalReceiverConn *wrconn, List *publications)
{
WalRcvExecResult *res;
- StringInfoData cmd;
+ StringInfoData cmd,
+ pub_names;
TupleTableSlot *slot;
Oid tableRow[3] = {TEXTOID, TEXTOID, NAMEARRAYOID};
List *tablelist = NIL;
- bool check_columnlist = (walrcv_server_version(wrconn) >= 150000);
+ int server_version = walrcv_server_version(wrconn);
+ bool check_columnlist = (server_version >= 150000);
- initStringInfo(&cmd);
- appendStringInfoString(&cmd, "SELECT DISTINCT t.schemaname, t.tablename \n");
+ initStringInfo(&pub_names);
+ get_publications_str(publications, &pub_names, true);
- /* Get column lists for each relation if the publisher supports it */
- if (check_columnlist)
- appendStringInfoString(&cmd, ", t.attnames\n");
+ initStringInfo(&cmd);
- appendStringInfoString(&cmd, "FROM pg_catalog.pg_publication_tables t\n"
- " WHERE t.pubname IN (");
- get_publications_str(publications, &cmd, true);
- appendStringInfoChar(&cmd, ')');
+ /*
+ * Get namespace, relname and column list (if supported) of the tables
+ * belonging to the specified publications.
+ *
+ * Get the list of tables from the publisher. The partition table whose
+ * ancestor is also in this list will be ignored, otherwise the initial
+ * data in the partition table would be replicated twice.
+ *
+ * From version 16, the parameter of the function
+ * pg_get_publication_tables can be an array of publications. The
+ * partition table whose ancestor is also published in this publication
+ * array will be filtered out in this function.
+ */
+ if (server_version >= 160000)
+ {
+ appendStringInfo(&cmd, "SELECT DISTINCT N.nspname, C.relname,\n"
+ " ( SELECT array_agg(a.attname ORDER BY a.attnum)\n"
+ " FROM pg_attribute a\n"
+ " WHERE a.attrelid = GPT.relid AND a.attnum > 0 AND\n"
+ " NOT a.attisdropped AND\n"
+ " (a.attnum = ANY(GPT.attrs) OR GPT.attrs IS NULL)\n"
+ " ) AS attnames\n"
+ " FROM pg_class C\n"
+ " JOIN pg_namespace N ON N.oid = C.relnamespace\n"
+ " JOIN ( SELECT (pg_get_publication_tables(VARIADIC array_agg(pubname::text))).*\n"
+ " FROM pg_publication\n"
+ " WHERE pubname IN ( %s )) as GPT\n"
+ " ON GPT.relid = C.oid\n",
+ pub_names.data);
+ }
+ else
+ {
+ appendStringInfoString(&cmd, "WITH pub_tabs AS(\n"
+ " SELECT DISTINCT N.nspname, C.oid, C.relname, C.relispartition\n");
+
+ /* Get column lists for each relation if the publisher supports it */
+ if (check_columnlist)
+ appendStringInfoString(&cmd, ", ( SELECT array_agg(a.attname ORDER BY a.attnum)\n"
+ " FROM pg_attribute a\n"
+ " WHERE a.attrelid = GPT.relid AND a.attnum > 0 AND\n"
+ " NOT a.attisdropped AND\n"
+ " (a.attnum = ANY(GPT.attrs) OR GPT.attrs IS NULL)\n"
+ " ) AS attnames\n");
+
+ appendStringInfo(&cmd, " FROM pg_publication P,\n"
+ " LATERAL pg_get_publication_tables(P.pubname) GPT,\n"
+ " pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)\n"
+ " WHERE C.oid = GPT.relid AND P.pubname IN ( %s )\n"
+ ")\n"
+ "SELECT DISTINCT pub_tabs.nspname, pub_tabs.relname\n",
+ pub_names.data);
+
+ /* Get column lists for each relation if the publisher supports it */
+ if (check_columnlist)
+ appendStringInfoString(&cmd, ", pub_tabs.attnames\n");
+
+ appendStringInfoString(&cmd, "FROM pub_tabs\n"
+ " WHERE (pub_tabs.relispartition IS FALSE\n"
+ " OR NOT EXISTS (SELECT 1 FROM pg_partition_ancestors(pub_tabs.oid) as PA\n"
+ " WHERE PA.relid IN (SELECT pub_tabs.oid FROM pub_tabs)\n"
+ " AND PA.relid != pub_tabs.oid))\n");
+ }
res = walrcv_exec(wrconn, cmd.data, check_columnlist ? 3 : 2, tableRow);
+ pfree(pub_names.data);
pfree(cmd.data);
if (res->status != WALRCV_OK_TUPLES)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8b72f8a215..80bb1408c3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11607,11 +11607,13 @@
prosrc => 'pg_show_replication_origin_status' },
# publications
-{ oid => '6119', descr => 'get information of tables in a publication',
- proname => 'pg_get_publication_tables', prorows => '1000', proretset => 't',
- provolatile => 's', prorettype => 'record', proargtypes => 'text',
- proallargtypes => '{text,oid,int2vector,pg_node_tree}',
- proargmodes => '{i,o,o,o}', proargnames => '{pubname,relid,attrs,qual}',
+{ oid => '6119',
+ descr => 'get information of the tables in the given publication array',
+ proname => 'pg_get_publication_tables', prorows => '1000',
+ provariadic => 'text', proretset => 't', provolatile => 's',
+ prorettype => 'record', proargtypes => '_text',
+ proallargtypes => '{_text,oid,int2vector,pg_node_tree}',
+ proargmodes => '{v,o,o,o}', proargnames => '{pubname,relid,attrs,qual}',
prosrc => 'pg_get_publication_tables' },
{ oid => '6121',
descr => 'returns whether a relation can be part of a publication',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9dd137415e..b90c629c3a 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1444,7 +1444,7 @@ pg_publication_tables| SELECT p.pubname,
WHERE ((a.attrelid = gpt.relid) AND (a.attnum > 0) AND (NOT a.attisdropped) AND ((a.attnum = ANY ((gpt.attrs)::smallint[])) OR (gpt.attrs IS NULL)))) AS attnames,
pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
FROM pg_publication p,
- LATERAL pg_get_publication_tables((p.pubname)::text) gpt(relid, attrs, qual),
+ LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(relid, attrs, qual),
(pg_class c
JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.oid = gpt.relid);
diff --git a/src/test/subscription/t/013_partition.pl b/src/test/subscription/t/013_partition.pl
index 8b33e4e7ae..100e2d2fd0 100644
--- a/src/test/subscription/t/013_partition.pl
+++ b/src/test/subscription/t/013_partition.pl
@@ -473,12 +473,12 @@ $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 (tab4) here. We specified
+# publish_via_partition_root = true (see pub_all and pub_lower_level above), so
+# all data will be replicated to that 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).
@@ -550,11 +550,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',
@@ -577,11 +572,6 @@ $result =
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");
diff --git a/src/test/subscription/t/028_row_filter.pl b/src/test/subscription/t/028_row_filter.pl
index da52289dde..056ceb8096 100644
--- a/src/test/subscription/t/028_row_filter.pl
+++ b/src/test/subscription/t/028_row_filter.pl
@@ -386,6 +386,10 @@ $node_publisher->safe_psql('postgres',
"INSERT INTO tab_rowfilter_child(a, b) VALUES(0,'0'),(30,'30'),(40,'40')"
);
+# insert data into partitioned table.
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_rowfilter_viaroot_part(a) VALUES(13), (17)");
+
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_1, tap_pub_2, tap_pub_3, tap_pub_4a, tap_pub_4b, tap_pub_5a, tap_pub_5b, tap_pub_toast, tap_pub_inherits, tap_pub_viaroot_2, tap_pub_viaroot_1"
);
@@ -707,13 +711,18 @@ is($result, qq(t|1), 'check replicated rows to tab_rowfilter_toast');
# the row filter for the top-level ancestor:
#
# tab_rowfilter_viaroot_part filter is: (a > 15)
+# - INSERT (13) NO, 13 < 15
# - INSERT (14) NO, 14 < 15
# - INSERT (15) NO, 15 = 15
# - INSERT (16) YES, 16 > 15
+# - INSERT (17) YES, 17 > 15
$result =
$node_subscriber->safe_psql('postgres',
- "SELECT a FROM tab_rowfilter_viaroot_part");
-is($result, qq(16), 'check replicated rows to tab_rowfilter_viaroot_part');
+ "SELECT a FROM tab_rowfilter_viaroot_part ORDER BY 1");
+is($result, qq(16
+17),
+ 'check replicated rows to tab_rowfilter_viaroot_part'
+);
# Check there is no data in tab_rowfilter_viaroot_part_1 because rows are
# replicated via the top most parent table tab_rowfilter_viaroot_part
diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl
index 3e4bfc2178..fb05d01fd0 100644
--- a/src/test/subscription/t/031_column_list.pl
+++ b/src/test/subscription/t/031_column_list.pl
@@ -959,7 +959,8 @@ $node_publisher->safe_psql(
CREATE TABLE test_root_1 PARTITION OF test_root FOR VALUES FROM (1) TO (10);
CREATE TABLE test_root_2 PARTITION OF test_root FOR VALUES FROM (10) TO (20);
- CREATE PUBLICATION pub_root_true FOR TABLE test_root (a) WITH (publish_via_partition_root = true);
+ CREATE PUBLICATION pub_root_true_1 FOR TABLE test_root (a) WITH (publish_via_partition_root = true);
+ CREATE PUBLICATION pub_root_true_2 FOR TABLE test_root_1 (a, b) WITH (publish_via_partition_root = true);
-- initial data
INSERT INTO test_root VALUES (1, 2, 3);
@@ -968,7 +969,7 @@ $node_publisher->safe_psql(
$node_subscriber->safe_psql(
'postgres', qq(
- CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub_root_true;
+ CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub_root_true_1, pub_root_true_2;
));
$node_subscriber->wait_for_subscription_sync;
--
2.31.1
[application/octet-stream] REL15_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch (8.6K, ../OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com/3-REL15_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch)
download
[application/octet-stream] REL14_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch (5.3K, ../OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com/4-REL14_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch)
download
[application/octet-stream] REL13_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch (5.3K, ../OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com/5-REL13_v12-0001-Fix-data-replicated-twice-when-specifying-publis_patch)
download
view thread (99+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Data is copied twice when specifying both child and parent table in publication
In-Reply-To: <OS3PR01MB6275A9B8C65C381C6828DF9D9E549@OS3PR01MB6275.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox