Re: Why is subscription/t/031_column_list.pl failing so much?

[Alexander Lakhin]

Hello Amit,

05.02.2024 15:20, Amit Kapila wrote:

If this can be reproduced frequently then we can even try to test the
patch in that thread by Osumi-San [1] (I haven't tested that it
applies cleanly but shouldn't be difficult to make it work) based on
the theory that walsender is starting up at an LSN somewhere before
where the publication is created.

[1] - 
https://www.postgresql.org/message-id/TYCPR01MB83737A68CD5D554EA82BD7B9EDD39%40TYCPR01MB8373.jpnprd01.prod.outlook.com

Yes, with the aforementioned modification of bgwriter.c and when running
20 tests in parallel, I got failures on iterations 20. 3, 21 ..., but with the
updated Osumi-San's patch (which adds wait_for_catchup('sub1') before every
ALTER SUBSCRIPTION sub1 SET PUBLICATION ...) applied, 300 iterations ran
with no failures.

Best regards,
Alexander
diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl
index 938582e31a..b199c8a1bf 100644
--- a/src/test/subscription/t/031_column_list.pl
+++ b/src/test/subscription/t/031_column_list.pl
@@ -368,6 +368,8 @@ $node_subscriber->safe_psql(
 	CREATE TABLE tab5 (a int PRIMARY KEY, b int, d int);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	ALTER SUBSCRIPTION sub1 SET PUBLICATION pub2, pub3
@@ -409,6 +411,8 @@ $node_subscriber->safe_psql(
 	CREATE TABLE tab6 (a int PRIMARY KEY, b int, c int, d int);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	ALTER SUBSCRIPTION sub1 SET PUBLICATION pub4
@@ -485,6 +489,8 @@ $node_subscriber->safe_psql(
 	CREATE TABLE tab7 (a int PRIMARY KEY, b int, c int, d int);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	ALTER SUBSCRIPTION sub1 SET PUBLICATION pub5
@@ -601,6 +607,8 @@ $node_publisher->safe_psql(
 	ALTER PUBLICATION pub6 ADD TABLE test_part_a_2 (b);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 # add the publication to our subscription, wait for sync to complete
 $node_subscriber->safe_psql(
 	'postgres', qq(
@@ -667,6 +675,8 @@ $node_publisher->safe_psql(
 	CREATE PUBLICATION pub7 FOR TABLE test_part_b (a, b) WITH (publish_via_partition_root = true);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 # add the publication to our subscription, wait for sync to complete
 $node_subscriber->safe_psql(
 	'postgres', qq(
@@ -835,6 +845,8 @@ $node_publisher->safe_psql(
 	CREATE PUBLICATION pub9 FOR TABLE test_part_d (a) WITH (publish_via_partition_root = true);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 # add the publication to our subscription, wait for sync to complete
 $node_subscriber->safe_psql(
 	'postgres', qq(
@@ -877,6 +889,8 @@ $node_publisher->safe_psql(
 	INSERT INTO test_mix_2 VALUES (1, 2, 3);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	CREATE TABLE test_mix_2 (a int PRIMARY KEY, b int, c int);
@@ -1016,6 +1030,8 @@ $node_publisher->safe_psql(
 	INSERT INTO s1.t VALUES (1, 2, 3);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	CREATE SCHEMA s1;
@@ -1039,6 +1055,8 @@ is( $node_subscriber->safe_psql('postgres', "SELECT * FROM s1.t ORDER BY a"),
 4|5|6),
 	'two publications, publishing the same relation');
 
+$node_publisher->wait_for_catchup('sub1');
+
 # Now resync the subscription, but with publications in the opposite order.
 # The result should be the same.
 
@@ -1082,6 +1100,8 @@ $node_publisher->safe_psql(
 	INSERT INTO t VALUES (1, 2, 3);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	DROP SCHEMA s1 CASCADE;
@@ -1130,6 +1150,8 @@ $node_publisher->safe_psql(
 	INSERT INTO t VALUES (1, 2, 3);
 ));
 
+$node_publisher->wait_for_catchup('sub1');
+
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	DROP TABLE t;