v2-0001-Fix-testcase.patch

From a9ad4dfd5d6dbe5e02e5a9fbf7b7710d0f5a45b4 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Date: Wed, 14 Feb 2024 03:01:08 +0000
Subject: [PATCH v2] Fix testcase

---
 src/bin/pg_upgrade/t/004_subscription.pl | 304 +++++++++++------------
 1 file changed, 149 insertions(+), 155 deletions(-)

diff --git a/src/bin/pg_upgrade/t/004_subscription.pl b/src/bin/pg_upgrade/t/004_subscription.pl
index 63c0a98376..5780cb6fb4 100644
--- a/src/bin/pg_upgrade/t/004_subscription.pl
+++ b/src/bin/pg_upgrade/t/004_subscription.pl
@@ -5,6 +5,7 @@ use strict;
 use warnings FATAL => 'all';
 
 use File::Find qw(find);
+use File::Path qw(rmtree);
 
 use PostgreSQL::Test::Cluster;
 use PostgreSQL::Test::Utils;
@@ -49,13 +50,140 @@ $old_sub->safe_psql(
 # Setup logical replication
 my $connstr = $publisher->connstr . ' dbname=postgres';
 
-# Setup an enabled subscription to verify that the running status and failover
-# option are retained after the upgrade.
+# Setup a subscription to verify that the failover option are retained after
+# the upgrade.
 $publisher->safe_psql('postgres', "CREATE PUBLICATION regress_pub1");
 $old_sub->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub1 CONNECTION '$connstr' PUBLICATION regress_pub1 WITH (failover = true)"
+	"CREATE SUBSCRIPTION regress_sub1 CONNECTION '$connstr' PUBLICATION regress_pub1 WITH (failover = true, enabled = false)"
 );
-$old_sub->wait_for_subscription_sync($publisher, 'regress_sub1');
+
+$old_sub->stop;
+
+# ------------------------------------------------------
+# Check that pg_upgrade fails when max_replication_slots configured in the new
+# cluster is less than the number of subscriptions in the old cluster.
+# ------------------------------------------------------
+$new_sub->append_conf('postgresql.conf', "max_replication_slots = 0");
+
+# pg_upgrade will fail because the new cluster has insufficient
+# max_replication_slots.
+command_checks_all(
+	[
+		'pg_upgrade', '--no-sync', '-d', $old_sub->data_dir,
+		'-D', $new_sub->data_dir, '-b', $oldbindir,
+		'-B', $newbindir, '-s', $new_sub->host,
+		'-p', $old_sub->port, '-P', $new_sub->port,
+		$mode, '--check',
+	],
+	1,
+	[
+		qr/max_replication_slots \(0\) must be greater than or equal to the number of subscriptions \(1\) on the old cluster/
+	],
+	[qr//],
+	'run of pg_upgrade where the new cluster has insufficient max_replication_slots'
+);
+
+# Reset max_replication_slots
+$new_sub->append_conf('postgresql.conf', "max_replication_slots = 10");
+
+$old_sub->start;
+
+# ------------------------------------------------------
+# Check that pg_upgrade refuses to run if:
+# a) there's a subscription with tables in a state other than 'r' (ready) or
+#    'i' (init) and/or
+# b) the subscription has no replication origin.
+# ------------------------------------------------------
+$publisher->safe_psql(
+	'postgres', qq[
+		CREATE TABLE tab_primary_key(id serial PRIMARY KEY);
+		INSERT INTO tab_primary_key values(1);
+		ALTER PUBLICATION regress_pub1 ADD TABLE tab_primary_key;
+]);
+
+# Insert the same value that is already present in publisher to the primary key
+# column of subscriber so that the table sync will fail.
+$old_sub->safe_psql(
+	'postgres', qq[
+		CREATE TABLE tab_primary_key(id serial PRIMARY KEY);
+		INSERT INTO tab_primary_key values(1);
+		ALTER SUBSCRIPTION regress_sub1 ENABLE;
+		ALTER SUBSCRIPTION regress_sub1 REFRESH PUBLICATION;
+]);
+
+# Table will be in 'd' (data is being copied) state as table sync will fail
+# because of primary key constraint error.
+my $started_query =
+  "SELECT count(1) = 1 FROM pg_subscription_rel WHERE srsubstate = 'd'";
+$old_sub->poll_query_until('postgres', $started_query)
+  or die
+  "Timed out while waiting for the table state to become 'd' (datasync)";
+
+# Create another subscription and drop the subscription's replication origin
+$old_sub->safe_psql('postgres',
+	"CREATE SUBSCRIPTION regress_sub2 CONNECTION '$connstr' PUBLICATION regress_pub1 WITH (enabled = false)"
+);
+my $sub_oid = $old_sub->safe_psql('postgres',
+	"SELECT oid FROM pg_subscription WHERE subname = 'regress_sub2'");
+my $reporigin = 'pg_' . qq($sub_oid);
+$old_sub->safe_psql('postgres',
+	"SELECT pg_replication_origin_drop('$reporigin')");
+
+$old_sub->stop;
+
+command_fails(
+	[
+		'pg_upgrade', '--no-sync', '-d', $old_sub->data_dir,
+		'-D', $new_sub->data_dir, '-b', $oldbindir,
+		'-B', $newbindir, '-s', $new_sub->host,
+		'-p', $old_sub->port, '-P', $new_sub->port,
+		$mode, '--check',
+	],
+	'run of pg_upgrade --check for old instance with relation in \'d\' datasync(invalid) state and missing replication origin'
+);
+
+# Verify the reason why the subscriber cannot be upgraded
+my $sub_relstate_filename;
+
+# Find a txt file that contains a list of tables that cannot be upgraded. We
+# cannot predict the file's path because the output directory contains a
+# milliseconds timestamp. File::Find::find must be used.
+find(
+	sub {
+		if ($File::Find::name =~ m/subs_invalid\.txt/)
+		{
+			$sub_relstate_filename = $File::Find::name;
+		}
+	},
+	$new_sub->data_dir . "/pg_upgrade_output.d");
+
+# Check the file content which should have tab_primary_key table in invalid
+# state.
+like(
+	slurp_file($sub_relstate_filename),
+	qr/The table sync state \"d\" is not allowed for database:\"postgres\" subscription:\"regress_sub1\" schema:\"public\" relation:\"tab_primary_key\"/m,
+	'the previous test failed due to subscription table in invalid state');
+
+# Check the file content which should have regress_sub2 subscription.
+like(
+	slurp_file($sub_relstate_filename),
+	qr/The replication origin is missing for database:\"postgres\" subscription:\"regress_sub2\"/m,
+	'the previous test failed due to missing replication origin');
+
+# cleanup
+$old_sub->start;
+$publisher->safe_psql(
+	'postgres', qq[
+		ALTER PUBLICATION regress_pub1 DROP TABLE tab_primary_key;
+		DROP TABLE tab_primary_key;
+]);
+$old_sub->safe_psql(
+	'postgres', qq[
+		DROP SUBSCRIPTION regress_sub2;
+		ALTER SUBSCRIPTION regress_sub1 REFRESH PUBLICATION;
+		DROP TABLE tab_primary_key;
+]);
+rmtree($new_sub->data_dir . "/pg_upgrade_output.d");
 
 # Verify that the upgrade should be successful with tables in 'ready'/'init'
 # state along with retaining the replication origin's remote lsn, and
@@ -63,7 +191,7 @@ $old_sub->wait_for_subscription_sync($publisher, 'regress_sub1');
 $publisher->safe_psql('postgres',
 	"CREATE PUBLICATION regress_pub2 FOR TABLE tab_upgraded1");
 $old_sub->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub2 CONNECTION '$connstr' PUBLICATION regress_pub2"
+	"CREATE SUBSCRIPTION regress_sub3 CONNECTION '$connstr' PUBLICATION regress_pub2"
 );
 # Wait till the table tab_upgraded1 reaches 'ready' state
 my $synced_query =
@@ -73,7 +201,7 @@ $old_sub->poll_query_until('postgres', $synced_query)
 
 $publisher->safe_psql('postgres',
 	"INSERT INTO tab_upgraded1 VALUES (generate_series(1,50))");
-$publisher->wait_for_catchup('regress_sub2');
+$publisher->wait_for_catchup('regress_sub3');
 
 # Change configuration to prepare a subscription table in init state
 $old_sub->append_conf('postgresql.conf',
@@ -83,7 +211,7 @@ $old_sub->restart;
 $publisher->safe_psql('postgres',
 	"ALTER PUBLICATION regress_pub2 ADD TABLE tab_upgraded2");
 $old_sub->safe_psql('postgres',
-	"ALTER SUBSCRIPTION regress_sub2 REFRESH PUBLICATION");
+	"ALTER SUBSCRIPTION regress_sub3 REFRESH PUBLICATION");
 
 # The table tab_upgraded2 will be in init state as the subscriber
 # configuration for max_logical_replication_workers is set to 0.
@@ -93,10 +221,10 @@ is($result, qq(t), "Check that the table is in init state");
 
 # Get the replication origin's remote_lsn of the old subscriber
 my $remote_lsn = $old_sub->safe_psql('postgres',
-	"SELECT remote_lsn FROM pg_replication_origin_status os, pg_subscription s WHERE os.external_id = 'pg_' || s.oid AND s.subname = 'regress_sub2'"
+	"SELECT remote_lsn FROM pg_replication_origin_status os, pg_subscription s WHERE os.external_id = 'pg_' || s.oid AND s.subname = 'regress_sub3'"
 );
 # Have the subscription in disabled state before upgrade
-$old_sub->safe_psql('postgres', "ALTER SUBSCRIPTION regress_sub2 DISABLE");
+$old_sub->safe_psql('postgres', "ALTER SUBSCRIPTION regress_sub3 DISABLE");
 
 my $tab_upgraded1_oid = $old_sub->safe_psql('postgres',
 	"SELECT oid FROM pg_class WHERE relname = 'tab_upgraded1'");
@@ -139,16 +267,17 @@ $new_sub->start;
 
 # The subscription's running status and failover option should be preserved
 # in the upgraded instance. So regress_sub1 should still have subenabled and
-# subfailover set to true, while regress_sub2 should have both set to false.
-$result =
-  $new_sub->safe_psql('postgres',
-	"SELECT subname, subenabled, subfailover FROM pg_subscription ORDER BY subname");
+# subfailover set to true, while regress_sub3 should have both set to false.
+$result = $new_sub->safe_psql('postgres',
+	"SELECT subname, subenabled, subfailover FROM pg_subscription ORDER BY subname"
+);
 is( $result, qq(regress_sub1|t|t
-regress_sub2|f|f),
-	"check that the subscription's running status and failover are preserved");
+regress_sub3|f|f),
+	"check that the subscription's running status and failover are preserved"
+);
 
-my $sub_oid = $new_sub->safe_psql('postgres',
-	"SELECT oid FROM pg_subscription WHERE subname = 'regress_sub2'");
+$sub_oid = $new_sub->safe_psql('postgres',
+	"SELECT oid FROM pg_subscription WHERE subname = 'regress_sub3'");
 
 # Subscription relations should be preserved
 $result = $new_sub->safe_psql('postgres',
@@ -166,10 +295,10 @@ $result = $new_sub->safe_psql('postgres',
 is($result, qq($remote_lsn), "remote_lsn should have been preserved");
 
 # Enable the subscription
-$new_sub->safe_psql('postgres', "ALTER SUBSCRIPTION regress_sub2 ENABLE");
+$new_sub->safe_psql('postgres', "ALTER SUBSCRIPTION regress_sub3 ENABLE");
 
-# Wait until all tables of subscription 'regress_sub2' are synchronized
-$new_sub->wait_for_subscription_sync($publisher, 'regress_sub2');
+# Wait until all tables of subscription 'regress_sub3' are synchronized
+$new_sub->wait_for_subscription_sync($publisher, 'regress_sub3');
 
 # Rows on tab_upgraded1 and tab_upgraded2 should have been replicated
 $result =
@@ -181,139 +310,4 @@ is($result, qq(1),
 	"check the data is synced after enabling the subscription for the table that was in init state"
 );
 
-# cleanup
-$new_sub->stop;
-$old_sub->append_conf('postgresql.conf',
-	"max_logical_replication_workers = 4");
-$old_sub->start;
-$old_sub->safe_psql(
-	'postgres', qq[
-		ALTER SUBSCRIPTION regress_sub1 DISABLE;
-		ALTER SUBSCRIPTION regress_sub1 SET (slot_name = none);
-		DROP SUBSCRIPTION regress_sub1;
-]);
-$old_sub->stop;
-
-# ------------------------------------------------------
-# Check that pg_upgrade fails when max_replication_slots configured in the new
-# cluster is less than the number of subscriptions in the old cluster.
-# ------------------------------------------------------
-my $new_sub1 = PostgreSQL::Test::Cluster->new('new_sub1');
-$new_sub1->init;
-$new_sub1->append_conf('postgresql.conf', "max_replication_slots = 0");
-
-# pg_upgrade will fail because the new cluster has insufficient
-# max_replication_slots.
-command_checks_all(
-	[
-		'pg_upgrade', '--no-sync',
-		'-d', $old_sub->data_dir,
-		'-D', $new_sub1->data_dir,
-		'-b', $oldbindir,
-		'-B', $newbindir,
-		'-s', $new_sub1->host,
-		'-p', $old_sub->port,
-		'-P', $new_sub1->port,
-		$mode, '--check',
-	],
-	1,
-	[
-		qr/max_replication_slots \(0\) must be greater than or equal to the number of subscriptions \(1\) on the old cluster/
-	],
-	[qr//],
-	'run of pg_upgrade where the new cluster has insufficient max_replication_slots'
-);
-
-# Reset max_replication_slots
-$new_sub1->append_conf('postgresql.conf', "max_replication_slots = 10");
-
-# Drop the subscription
-$old_sub->start;
-$old_sub->safe_psql('postgres', "DROP SUBSCRIPTION regress_sub2");
-
-# ------------------------------------------------------
-# Check that pg_upgrade refuses to run if:
-# a) there's a subscription with tables in a state other than 'r' (ready) or
-#    'i' (init) and/or
-# b) the subscription has no replication origin.
-# ------------------------------------------------------
-$publisher->safe_psql(
-	'postgres', qq[
-		CREATE TABLE tab_primary_key(id serial PRIMARY KEY);
-		INSERT INTO tab_primary_key values(1);
-		CREATE PUBLICATION regress_pub3 FOR TABLE tab_primary_key;
-]);
-
-# Insert the same value that is already present in publisher to the primary key
-# column of subscriber so that the table sync will fail.
-$old_sub->safe_psql(
-	'postgres', qq[
-		CREATE TABLE tab_primary_key(id serial PRIMARY KEY);
-		INSERT INTO tab_primary_key values(1);
-		CREATE SUBSCRIPTION regress_sub3 CONNECTION '$connstr' PUBLICATION regress_pub3;
-]);
-
-# Table will be in 'd' (data is being copied) state as table sync will fail
-# because of primary key constraint error.
-my $started_query =
-  "SELECT count(1) = 1 FROM pg_subscription_rel WHERE srsubstate = 'd'";
-$old_sub->poll_query_until('postgres', $started_query)
-  or die
-  "Timed out while waiting for the table state to become 'd' (datasync)";
-
-# Create another subscription and drop the subscription's replication origin
-$old_sub->safe_psql('postgres',
-	"CREATE SUBSCRIPTION regress_sub4 CONNECTION '$connstr' PUBLICATION regress_pub3 WITH (enabled = false)"
-);
-$sub_oid = $old_sub->safe_psql('postgres',
-	"SELECT oid FROM pg_subscription WHERE subname = 'regress_sub4'");
-my $reporigin = 'pg_' . qq($sub_oid);
-$old_sub->safe_psql('postgres',
-	"SELECT pg_replication_origin_drop('$reporigin')");
-
-$old_sub->stop;
-
-command_fails(
-	[
-		'pg_upgrade', '--no-sync',
-		'-d', $old_sub->data_dir,
-		'-D', $new_sub1->data_dir,
-		'-b', $oldbindir,
-		'-B', $newbindir,
-		'-s', $new_sub1->host,
-		'-p', $old_sub->port,
-		'-P', $new_sub1->port,
-		$mode, '--check',
-	],
-	'run of pg_upgrade --check for old instance with relation in \'d\' datasync(invalid) state and missing replication origin'
-);
-
-# Verify the reason why the subscriber cannot be upgraded
-my $sub_relstate_filename;
-
-# Find a txt file that contains a list of tables that cannot be upgraded. We
-# cannot predict the file's path because the output directory contains a
-# milliseconds timestamp. File::Find::find must be used.
-find(
-	sub {
-		if ($File::Find::name =~ m/subs_invalid\.txt/)
-		{
-			$sub_relstate_filename = $File::Find::name;
-		}
-	},
-	$new_sub1->data_dir . "/pg_upgrade_output.d");
-
-# Check the file content which should have tab_primary_key table in invalid
-# state.
-like(
-	slurp_file($sub_relstate_filename),
-	qr/The table sync state \"d\" is not allowed for database:\"postgres\" subscription:\"regress_sub3\" schema:\"public\" relation:\"tab_primary_key\"/m,
-	'the previous test failed due to subscription table in invalid state');
-
-# Check the file content which should have regress_sub4 subscription.
-like(
-	slurp_file($sub_relstate_filename),
-	qr/The replication origin is missing for database:\"postgres\" subscription:\"regress_sub4\"/m,
-	'the previous test failed due to missing replication origin');
-
 done_testing();
-- 
2.43.0