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

Commit3f06c00

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 parenta96a1d6 commit3f06c00

File tree

4 files changed

+79
-4
lines changed

4 files changed

+79
-4
lines changed

‎src/backend/catalog/pg_publication.c

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,45 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
105105
relid >=FirstNormalObjectId;
106106
}
107107

108+
/*
109+
* Filter out the partitions whose parent tables were also specified in
110+
* the publication.
111+
*/
112+
staticList*
113+
filter_partitions(List*relids)
114+
{
115+
List*result=NIL;
116+
ListCell*lc;
117+
ListCell*lc2;
118+
119+
foreach(lc,relids)
120+
{
121+
boolskip= false;
122+
List*ancestors=NIL;
123+
Oidrelid=lfirst_oid(lc);
124+
125+
if (get_rel_relispartition(relid))
126+
ancestors=get_partition_ancestors(relid);
127+
128+
foreach(lc2,ancestors)
129+
{
130+
Oidancestor=lfirst_oid(lc2);
131+
132+
/* Check if the parent table exists in the published table list. */
133+
if (list_member_oid(relids,ancestor))
134+
{
135+
skip= true;
136+
break;
137+
}
138+
}
139+
140+
if (!skip)
141+
result=lappend_oid(result,relid);
142+
}
143+
144+
returnresult;
145+
}
146+
108147
/*
109148
* Another variant of this, taking a Relation.
110149
*/
@@ -557,10 +596,23 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
557596
if (publication->alltables)
558597
tables=GetAllTablesPublicationRelations(publication->pubviaroot);
559598
else
599+
{
560600
tables=GetPublicationRelations(publication->oid,
561601
publication->pubviaroot ?
562602
PUBLICATION_PART_ROOT :
563603
PUBLICATION_PART_LEAF);
604+
605+
/*
606+
* If the publication publishes partition changes via their
607+
* respective root partitioned tables, we must exclude partitions
608+
* in favor of including the root partitioned tables. Otherwise,
609+
* the function could return both the child and parent tables
610+
* which could cause data of the child table to be
611+
* double-published on the subscriber side.
612+
*/
613+
if (publication->pubviaroot)
614+
tables=filter_partitions(tables);
615+
}
564616
funcctx->user_fctx= (void*)tables;
565617

566618
MemoryContextSwitchTo(oldcontext);

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -163,6 +163,15 @@ HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
163163
ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
164164
-- works again, because update is no longer replicated
165165
UPDATE testpub_parted2 SET a = 2;
166+
-- publication includes both the parent table and the child table
167+
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted, testpub_parted2;
168+
-- only parent is listed as being in publication, not the partition
169+
SELECT * FROM pg_publication_tables;
170+
pubname | schemaname | tablename
171+
-------------------+------------+----------------
172+
testpub_forparted | public | testpub_parted
173+
(1 row)
174+
166175
DROP TABLE testpub_parted1, testpub_parted2;
167176
DROP PUBLICATION testpub_forparted, testpub_forparted1;
168177
-- Test cache invalidation FOR ALL TABLES publication

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,10 @@ UPDATE testpub_parted2 SET a = 2;
9797
ALTER PUBLICATION testpub_forpartedDROPTABLEtestpub_parted;
9898
-- works again, because update is no longer replicated
9999
UPDATE testpub_parted2SET a=2;
100+
-- publication includes both the parent table and the child table
101+
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted, testpub_parted2;
102+
-- only parent is listed as being in publication, not the partition
103+
SELECT*FROM pg_publication_tables;
100104
DROPTABLE testpub_parted1, testpub_parted2;
101105
DROP PUBLICATION testpub_forparted, testpub_forparted1;
102106

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

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
use warnings;
44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>62;
6+
use Test::Moretests=>63;
77

88
# setup
99

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

419+
# prepare data for the initial sync
420+
$node_publisher->safe_psql('postgres',"INSERT INTO tab2 VALUES (1)");
421+
417422
# subscriber 1
418423
$node_subscriber1->safe_psql('postgres',"DROP SUBSCRIPTION sub1");
419424
$node_subscriber1->safe_psql('postgres',
@@ -465,12 +470,17 @@ BEGIN
465470
$node_subscriber2->poll_query_until('postgres',$synced_query)
466471
ordie"Timed out while waiting for subscriber to synchronize data";
467472

473+
# check that data is synced correctly
474+
$result =$node_subscriber1->safe_psql('postgres',
475+
"SELECT c, a FROM tab2");
476+
is($result,qq(sub1_tab2|1),'initial data synced for pub_viaroot');
477+
468478
# insert
469479
$node_publisher->safe_psql('postgres',"INSERT INTO tab1 VALUES (1), (0)");
470480
$node_publisher->safe_psql('postgres',"INSERT INTO tab1_1 (a) VALUES (3)");
471481
$node_publisher->safe_psql('postgres',"INSERT INTO tab1_2 VALUES (5)");
472482
$node_publisher->safe_psql('postgres',
473-
"INSERT INTO tab2 VALUES (1), (0), (3), (5)");
483+
"INSERT INTO tab2 VALUES (0), (3), (5)");
474484
$node_publisher->safe_psql('postgres',
475485
"INSERT INTO tab3 VALUES (1), (0), (3), (5)");
476486

0 commit comments

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