On Wed, 7 Feb 2024 at 15:21, Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Tue, Feb 6, 2024 at 8:21 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Amit Kapila <amit.kapil...@gmail.com> writes: > > > Yeah, I was worried about that. The other idea I have previously > > > thought was to change Alter Subscription to Drop+Create Subscription. > > > That should also help in bringing stability without losing any > > > functionality. > > > > Hm, why would that fix it? > > > > Because for new subscriptions, we will start reading WAL from the > latest WAL insert pointer on the publisher which will be after the > point where publication is created.
I was able to reproduce the issue consistently with the changes shared by Tom Lane at [1]. I have made changes to change ALTER SUBSCRIPTION to DROP+CREATE SUBSCRIPTION and verified that the test has passed consistently for >50 runs that I ran. Also the test execution time increased for this case is very negligibly: Without patch: 7.991 seconds With test change patch: 8.121 seconds The test changes for the same are attached. [1] - https://www.postgresql.org/message-id/631312.1707251789%40sss.pgh.pa.us Regards, Vignesh
From 35e31ca3d843d81b43a01d537073174f30f5097c Mon Sep 17 00:00:00 2001 From: Vignesh C <vignes...@gmail.com> Date: Wed, 7 Feb 2024 15:49:00 +0530 Subject: [PATCH v3] Fix the intermittent buildfarm failures in 031_column_list test. Fix the intermittent buildfarm failures in 031_column_list test. --- src/test/subscription/t/031_column_list.pl | 43 ++++++++++++++-------- 1 file changed, 28 insertions(+), 15 deletions(-) diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl index 938582e31a..9a97fa5020 100644 --- a/src/test/subscription/t/031_column_list.pl +++ b/src/test/subscription/t/031_column_list.pl @@ -370,7 +370,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub2, pub3 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub2, pub3 )); $node_subscriber->wait_for_subscription_sync($node_publisher, 'sub1'); @@ -411,7 +412,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub4 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub4 )); $node_subscriber->wait_for_subscription_sync; @@ -487,7 +489,8 @@ $node_subscriber->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub5 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub5 )); $node_subscriber->wait_for_subscription_sync; @@ -601,10 +604,12 @@ $node_publisher->safe_psql( ALTER PUBLICATION pub6 ADD TABLE test_part_a_2 (b); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub6 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub6 )); $node_subscriber->wait_for_subscription_sync; @@ -667,10 +672,12 @@ $node_publisher->safe_psql( CREATE PUBLICATION pub7 FOR TABLE test_part_b (a, b) WITH (publish_via_partition_root = true); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub7 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub7 )); $node_subscriber->wait_for_subscription_sync; @@ -737,7 +744,8 @@ $node_publisher->safe_psql( ALTER PUBLICATION pub8 ADD TABLE test_part_c_2 (a,b); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( DROP SUBSCRIPTION sub1; @@ -835,10 +843,12 @@ $node_publisher->safe_psql( CREATE PUBLICATION pub9 FOR TABLE test_part_d (a) WITH (publish_via_partition_root = true); )); -# add the publication to our subscription, wait for sync to complete +# create the subscription for the above publication, wait for sync to +# complete $node_subscriber->safe_psql( 'postgres', qq( - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub9 + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub9 )); $node_subscriber->wait_for_subscription_sync; @@ -880,8 +890,8 @@ $node_publisher->safe_psql( $node_subscriber->safe_psql( 'postgres', qq( CREATE TABLE test_mix_2 (a int PRIMARY KEY, b int, c int); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub_mix_3, pub_mix_4; - ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub_mix_3, pub_mix_4; )); $node_subscriber->wait_for_subscription_sync; @@ -1022,7 +1032,8 @@ $node_subscriber->safe_psql( CREATE TABLE s1.t (a int, b int, c int) PARTITION BY RANGE (a); CREATE TABLE t_1 PARTITION OF s1.t FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub1, pub2; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1, pub2; )); $node_subscriber->wait_for_subscription_sync; @@ -1090,7 +1101,8 @@ $node_subscriber->safe_psql( PARTITION BY RANGE (a); CREATE TABLE t_2 PARTITION OF t_1 FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub3; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub3; )); $node_subscriber->wait_for_subscription_sync; @@ -1138,7 +1150,8 @@ $node_subscriber->safe_psql( PARTITION BY RANGE (a); CREATE TABLE t_2 PARTITION OF t_1 FOR VALUES FROM (1) TO (10); - ALTER SUBSCRIPTION sub1 SET PUBLICATION pub4; + DROP SUBSCRIPTION sub1; + CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub4; )); $node_subscriber->wait_for_subscription_sync; -- 2.34.1
