Skip to content

Commit 1046a69

Browse files
author
Amit Kapila
committed
Fix Alter Subscription's Add/Drop Publication behavior.
The current refresh behavior tries to just refresh added/dropped publications but that leads to removing wrong tables from subscription. We can't refresh just the dropped publication because it is quite possible that some of the tables are removed from publication by that time and now those will remain as part of the subscription. Also, there is a chance that the tables that were part of the publication being dropped are also part of another publication, so we can't remove those. So, we decided that by default, add/drop commands will also act like REFRESH PUBLICATION which means they will refresh all the publications. We can keep the old behavior for "add publication" but it is better to be consistent with "drop publication". Author: Hou Zhijie Reviewed-by: Masahiko Sawada, Amit Kapila Backpatch-through: 14, where it was introduced Discussion: https://postgr.es/m/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parent 9bbf6f7 commit 1046a69

File tree

6 files changed

+105
-23
lines changed

6 files changed

+105
-23
lines changed

‎doc/src/sgml/ref/alter_subscription.sgml

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -111,9 +111,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
111111
publications, and <literal>DROP</literal> removes the publications from
112112
the list of publications. See <xref linkend="sql-createsubscription"/>
113113
for more information. By default, this command will also act like
114-
<literal>REFRESH PUBLICATION</literal>, except that in case of
115-
<literal>ADD</literal> or <literal>DROP</literal>, only the added or
116-
dropped publications are refreshed.
114+
<literal>REFRESH PUBLICATION</literal>.
117115
</para>
118116

119117
<para>
@@ -134,8 +132,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
134132
</variablelist>
135133

136134
Additionally, refresh options as described
137-
under <literal>REFRESH PUBLICATION</literal> may be specified,
138-
except in the case of <literal>DROP PUBLICATION</literal>.
135+
under <literal>REFRESH PUBLICATION</literal> may be specified.
139136
</para>
140137
</listitem>
141138
</varlistentry>

‎src/backend/commands/subscriptioncmds.c

Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1006,10 +1006,7 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
10061006
List *publist;
10071007
bool isadd = stmt->kind == ALTER_SUBSCRIPTION_ADD_PUBLICATION;
10081008

1009-
supported_opts = SUBOPT_REFRESH;
1010-
if (isadd)
1011-
supported_opts |= SUBOPT_COPY_DATA;
1012-
1009+
supported_opts = SUBOPT_REFRESH | SUBOPT_COPY_DATA;
10131010
parse_subscription_options(pstate, stmt->options,
10141011
supported_opts, &opts);
10151012

@@ -1042,8 +1039,8 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
10421039

10431040
PreventInTransactionBlock(isTopLevel, "ALTER SUBSCRIPTION with refresh");
10441041

1045-
/* Only refresh the added/dropped list of publications. */
1046-
sub->publications = stmt->publication;
1042+
/* Refresh the new list of publications. */
1043+
sub->publications = publist;
10471044

10481045
AlterSubscription_refresh(sub, opts.copy_data);
10491046
}

‎src/bin/psql/tab-complete.c

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1675,14 +1675,10 @@ psql_completion(const char *text, int start, int end)
16751675
else if (HeadMatches("ALTER", "SUBSCRIPTION", MatchAny) &&
16761676
TailMatches("ADD|DROP|SET", "PUBLICATION", MatchAny))
16771677
COMPLETE_WITH("WITH (");
1678-
/* ALTER SUBSCRIPTION <name> ADD|SET PUBLICATION <name> WITH ( */
1678+
/* ALTER SUBSCRIPTION <name> ADD|DROP|SET PUBLICATION <name> WITH ( */
16791679
else if (HeadMatches("ALTER", "SUBSCRIPTION", MatchAny) &&
1680-
TailMatches("ADD|SET", "PUBLICATION", MatchAny, "WITH", "("))
1680+
TailMatches("ADD|DROP|SET", "PUBLICATION", MatchAny, "WITH", "("))
16811681
COMPLETE_WITH("copy_data", "refresh");
1682-
/* ALTER SUBSCRIPTION <name> DROP PUBLICATION <name> WITH ( */
1683-
else if (HeadMatches("ALTER", "SUBSCRIPTION", MatchAny) &&
1684-
TailMatches("DROP", "PUBLICATION", MatchAny, "WITH", "("))
1685-
COMPLETE_WITH("refresh");
16861682

16871683
/* ALTER SCHEMA <name> */
16881684
else if (Matches("ALTER", "SCHEMA", MatchAny))

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

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -230,9 +230,6 @@ ERROR: cannot drop all the publications from a subscription
230230
-- fail - publication does not exist in subscription
231231
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub3 WITH (refresh = false);
232232
ERROR: publication "testpub3" is not in subscription "regress_testsub"
233-
-- fail - do not support copy_data option
234-
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1 WITH (refresh = false, copy_data = true);
235-
ERROR: unrecognized subscription parameter: "copy_data"
236233
-- ok - delete publications
237234
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false);
238235
\dRs+

‎src/test/regress/sql/subscription.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -171,9 +171,6 @@ ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub, testpub1, testpub2
171171
-- fail - publication does not exist in subscription
172172
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub3 WITH (refresh = false);
173173

174-
-- fail - do not support copy_data option
175-
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1 WITH (refresh = false, copy_data = true);
176-
177174
-- ok - delete publications
178175
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false);
179176

‎src/test/subscription/t/024_add_drop_pub.pl

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,98 @@
1+
2+
# Copyright (c) 2021, PostgreSQL Global Development Group
3+
4+
# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION
5+
use strict;
6+
use warnings;
7+
use PostgresNode;
8+
use TestLib;
9+
use Test::More tests => 3;
10+
11+
# Initialize publisher node
12+
my $node_publisher = PostgresNode->new('publisher');
13+
$node_publisher->init(allows_streaming => 'logical');
14+
$node_publisher->start;
15+
16+
# Create subscriber node
17+
my $node_subscriber = PostgresNode->new('subscriber');
18+
$node_subscriber->init(allows_streaming => 'logical');
19+
$node_subscriber->start;
20+
21+
# Create table on publisher
22+
$node_publisher->safe_psql('postgres', "CREATE TABLE tab_1 (a int)");
23+
$node_publisher->safe_psql('postgres',
24+
"INSERT INTO tab_1 SELECT generate_series(1,10)");
25+
26+
# Create table on subscriber
27+
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_1 (a int)");
28+
29+
# Setup logical replication
30+
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
31+
$node_publisher->safe_psql('postgres',
32+
"CREATE PUBLICATION tap_pub_1 FOR TABLE tab_1");
33+
$node_publisher->safe_psql('postgres',
34+
"CREATE PUBLICATION tap_pub_2");
35+
36+
$node_subscriber->safe_psql('postgres',
37+
"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub_1, tap_pub_2"
38+
);
39+
40+
# Wait for initial table sync to finish
41+
my $synced_query =
42+
"SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
43+
44+
$node_subscriber->poll_query_until('postgres', $synced_query)
45+
or die "Timed out while waiting for subscriber to synchronize data";
46+
47+
$node_publisher->wait_for_catchup('tap_sub');
48+
49+
# Check the initial data of tab_1 is copied to subscriber
50+
my $result = $node_subscriber->safe_psql('postgres',
51+
"SELECT count(*), min(a), max(a) FROM tab_1");
52+
is($result, qq(10|1|10), 'check initial data is copied to subscriber');
53+
54+
# Create a new table on publisher
55+
$node_publisher->safe_psql('postgres', "CREATE TABLE tab_2 (a int)");
56+
$node_publisher->safe_psql('postgres',
57+
"INSERT INTO tab_2 SELECT generate_series(1,10)");
58+
59+
# Create a new table on subscriber
60+
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_2 (a int)");
61+
62+
# Add the table to publication
63+
$node_publisher->safe_psql('postgres',
64+
"ALTER PUBLICATION tap_pub_2 ADD TABLE tab_2");
65+
66+
# Dropping tap_pub_1 will refresh the entire publication list
67+
$node_subscriber->safe_psql('postgres',
68+
"ALTER SUBSCRIPTION tap_sub DROP PUBLICATION tap_pub_1");
69+
70+
# Wait for initial table sync to finish
71+
$node_subscriber->poll_query_until('postgres', $synced_query)
72+
or die "Timed out while waiting for subscriber to synchronize data";
73+
74+
$node_publisher->wait_for_catchup('tap_sub');
75+
76+
# Check the initial data of tab_drop_refresh was copied to subscriber
77+
$result = $node_subscriber->safe_psql('postgres',
78+
"SELECT count(*), min(a), max(a) FROM tab_2");
79+
is($result, qq(10|1|10), 'check initial data is copied to subscriber');
80+
81+
# Re-adding tap_pub_1 will refresh the entire publication list
82+
$node_subscriber->safe_psql('postgres',
83+
"ALTER SUBSCRIPTION tap_sub ADD PUBLICATION tap_pub_1");
84+
85+
# Wait for initial table sync to finish
86+
$node_subscriber->poll_query_until('postgres', $synced_query)
87+
or die "Timed out while waiting for subscriber to synchronize data";
88+
89+
$node_publisher->wait_for_catchup('tap_sub');
90+
91+
# Check the initial data of tab_1 was copied to subscriber again
92+
$result = $node_subscriber->safe_psql('postgres',
93+
"SELECT count(*), min(a), max(a) FROM tab_1");
94+
is($result, qq(20|1|10), 'check initial data is copied to subscriber');
95+
96+
# shutdown
97+
$node_subscriber->stop('fast');
98+
$node_publisher->stop('fast');

0 commit comments

Comments
 (0)