Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5e97905

Browse files
author
Amit Kapila
committed
Fix double publish of child table's data.
We publish the child table's data twice for a publication that has bothchild and parent tables and is published with publish_via_partition_rootas true. This happens because subscribers will initiate synchronizationusing both parent and child tables, since it gets both as separate tablesin the initial table list.Ensure that pg_publication_tables returns only parent tables in suchcases.Author: Hou ZhijieReviewed-by: Greg Nancarrow, Amit Langote, Vignesh C, Amit KapilaBackpatch-through: 13Discussion:https://postgr.es/m/OS0PR01MB57167F45D481F78CDC5986F794B99@OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parentbcf6058 commit5e97905

File tree

4 files changed

+40
-31
lines changed

4 files changed

+40
-31
lines changed

‎src/backend/catalog/pg_publication.c

Lines changed: 14 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -142,7 +142,7 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
142142
* the publication.
143143
*/
144144
staticList*
145-
filter_partitions(List*relids,List*schemarelids)
145+
filter_partitions(List*relids)
146146
{
147147
List*result=NIL;
148148
ListCell*lc;
@@ -161,16 +161,8 @@ filter_partitions(List *relids, List *schemarelids)
161161
{
162162
Oidancestor=lfirst_oid(lc2);
163163

164-
/*
165-
* Check if the parent table exists in the published table list.
166-
*
167-
* XXX As of now, we do this if the partition relation or the
168-
* partition relation's ancestor is present in schema publication
169-
* relations.
170-
*/
171-
if (list_member_oid(relids,ancestor)&&
172-
(list_member_oid(schemarelids,relid)||
173-
list_member_oid(schemarelids,ancestor)))
164+
/* Check if the parent table exists in the published table list. */
165+
if (list_member_oid(relids,ancestor))
174166
{
175167
skip= true;
176168
break;
@@ -913,22 +905,17 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
913905
PUBLICATION_PART_ROOT :
914906
PUBLICATION_PART_LEAF);
915907
tables=list_concat_unique_oid(relids,schemarelids);
916-
if (schemarelids&&publication->pubviaroot)
917-
{
918-
/*
919-
* If the publication publishes partition changes via their
920-
* respective root partitioned tables, we must exclude
921-
* partitions in favor of including the root partitioned
922-
* tables. Otherwise, the function could return both the child
923-
* and parent tables which could cause data of the child table
924-
* to be double-published on the subscriber side.
925-
*
926-
* XXX As of now, we do this when a publication has associated
927-
* schema or for all tables publication. See
928-
* GetAllTablesPublicationRelations().
929-
*/
930-
tables=filter_partitions(tables,schemarelids);
931-
}
908+
909+
/*
910+
* If the publication publishes partition changes via their
911+
* respective root partitioned tables, we must exclude partitions
912+
* in favor of including the root partitioned tables. Otherwise,
913+
* the function could return both the child and parent tables
914+
* which could cause data of the child table to be
915+
* double-published on the subscriber side.
916+
*/
917+
if (publication->pubviaroot)
918+
tables=filter_partitions(tables);
932919
}
933920

934921
funcctx->user_fctx= (void*)tables;

‎src/test/regress/expected/publication.out

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -829,6 +829,14 @@ SELECT * FROM pg_publication_tables;
829829
pub | sch2 | tbl1_part1
830830
(1 row)
831831

832+
-- Table publication that includes both the parent table and the child table
833+
ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
834+
SELECT * FROM pg_publication_tables;
835+
pubname | schemaname | tablename
836+
---------+------------+-----------
837+
pub | sch1 | tbl1
838+
(1 row)
839+
832840
DROP PUBLICATION pub;
833841
-- Schema publication that does not include the schema that has the parent table
834842
CREATE PUBLICATION pub FOR ALL TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);

‎src/test/regress/sql/publication.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -474,6 +474,10 @@ DROP PUBLICATION pub;
474474
CREATE PUBLICATION pub FOR TABLEsch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
475475
SELECT*FROM pg_publication_tables;
476476

477+
-- Table publication that includes both the parent table and the child table
478+
ALTER PUBLICATION pub ADD TABLEsch1.tbl1;
479+
SELECT*FROM pg_publication_tables;
480+
477481
DROP PUBLICATION pub;
478482
-- Schema publication that does not include the schema that has the parent table
479483
CREATE PUBLICATION pub FOR ALL TABLESIN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);

‎src/test/subscription/t/013_partition.pl

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
use warnings;
77
use PostgreSQL::Test::Cluster;
88
use PostgreSQL::Test::Utils;
9-
use Test::Moretests=>62;
9+
use Test::Moretests=>63;
1010

1111
# setup
1212

@@ -412,11 +412,16 @@ BEGIN
412412
$node_publisher->safe_psql('postgres',
413413
"ALTER PUBLICATION pub_all SET (publish_via_partition_root = true)");
414414
# Note: tab3_1's parent is not in the publication, in which case its
415-
# changes are published using own identity.
415+
# changes are published using own identity. For tab2, even though both parent
416+
# and child tables are present but changes will be replicated via the parent's
417+
# identity and only once.
416418
$node_publisher->safe_psql('postgres',
417-
"CREATE PUBLICATION pub_viaroot FOR TABLE tab2, tab3_1 WITH (publish_via_partition_root = true)"
419+
"CREATE PUBLICATION pub_viaroot FOR TABLE tab2,tab2_1,tab3_1 WITH (publish_via_partition_root = true)"
418420
);
419421

422+
# prepare data for the initial sync
423+
$node_publisher->safe_psql('postgres',"INSERT INTO tab2 VALUES (1)");
424+
420425
# subscriber 1
421426
$node_subscriber1->safe_psql('postgres',"DROP SUBSCRIPTION sub1");
422427
$node_subscriber1->safe_psql('postgres',
@@ -468,12 +473,17 @@ BEGIN
468473
$node_subscriber2->poll_query_until('postgres',$synced_query)
469474
ordie"Timed out while waiting for subscriber to synchronize data";
470475

476+
# check that data is synced correctly
477+
$result =$node_subscriber1->safe_psql('postgres',
478+
"SELECT c, a FROM tab2");
479+
is($result,qq(sub1_tab2|1),'initial data synced for pub_viaroot');
480+
471481
# insert
472482
$node_publisher->safe_psql('postgres',"INSERT INTO tab1 VALUES (1), (0)");
473483
$node_publisher->safe_psql('postgres',"INSERT INTO tab1_1 (a) VALUES (3)");
474484
$node_publisher->safe_psql('postgres',"INSERT INTO tab1_2 VALUES (5)");
475485
$node_publisher->safe_psql('postgres',
476-
"INSERT INTO tab2 VALUES (1), (0), (3), (5)");
486+
"INSERT INTO tab2 VALUES (0), (3), (5)");
477487
$node_publisher->safe_psql('postgres',
478488
"INSERT INTO tab3 VALUES (1), (0), (3), (5)");
479489

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp