From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 6 Mar 2021 18:35:26 -0600
Subject: [PATCH v7 1/2] Move pg_upgrade kludges to sql script

NOTE, "IF EXISTS" isn't necessary in fa66b6dee
---
 src/bin/pg_upgrade/test-upgrade.sql | 52 +++++++++++++++++++++++++++++
 src/bin/pg_upgrade/test.sh          | 48 +-------------------------
 2 files changed, 53 insertions(+), 47 deletions(-)
 create mode 100644 src/bin/pg_upgrade/test-upgrade.sql

diff --git a/src/bin/pg_upgrade/test-upgrade.sql b/src/bin/pg_upgrade/test-upgrade.sql
new file mode 100644
index 0000000000..5d74232c2b
--- /dev/null
+++ b/src/bin/pg_upgrade/test-upgrade.sql
@@ -0,0 +1,52 @@
+-- This file has a bunch of kludges needed for testing upgrades across major versions
+-- It supports testing the most recent version of an old release (not any arbitrary minor version).
+
+SELECT
+	ver <= 804 AS oldpgversion_le84,
+	ver < 1000 AS oldpgversion_lt10,
+	ver < 1200 AS oldpgversion_lt12,
+	ver < 1400 AS oldpgversion_lt14
+	FROM (SELECT current_setting('server_version_num')::int/100 AS ver) AS v;
+\gset
+
+\if :oldpgversion_le84
+DROP FUNCTION public.myfunc(integer);
+\endif
+
+\if :oldpgversion_lt10
+-- last in 9.6 -- commit 5ded4bd21
+DROP FUNCTION public.oldstyle_length(integer, text);
+\endif
+
+\if :oldpgversion_lt14
+-- last in v13 commit 7ca37fb04
+DROP FUNCTION IF EXISTS public.putenv(text);
+
+-- last in v13 commit 76f412ab3
+-- public.!=- This one is only needed for v11+ ??
+-- Note, until v10, operators could only be dropped one at a time
+DROP OPERATOR public.#@# (pg_catalog.int8, NONE);
+DROP OPERATOR public.#%# (pg_catalog.int8, NONE);
+DROP OPERATOR public.!=- (pg_catalog.int8, NONE);
+DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);
+\endif
+
+\if :oldpgversion_lt12
+-- WITH OIDS is not supported anymore in v12, so remove support
+-- for any relations marked as such.
+DO $stmt$
+	DECLARE
+		rec text;
+	BEGIN
+	FOR rec in
+		SELECT oid::regclass::text
+		FROM pg_class
+		WHERE relname !~ '^pg_'
+			AND relhasoids
+			AND relkind in ('r','m')
+		ORDER BY 1
+	LOOP
+		execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';
+	END LOOP;
+	END; $stmt$;
+\endif
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 8593488907..46a1ebb4ab 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -181,53 +181,7 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 	# Before dumping, tweak the database of the old instance depending
 	# on its version.
 	if [ "$newsrc" != "$oldsrc" ]; then
-		fix_sql=""
-		# Get rid of objects not feasible in later versions
-		case $oldpgversion in
-			804??)
-				fix_sql="DROP FUNCTION public.myfunc(integer);"
-				;;
-		esac
-
-		# Last appeared in v9.6
-		if [ $oldpgversion -lt 100000 ]; then
-			fix_sql="$fix_sql
-					 DROP FUNCTION IF EXISTS
-						public.oldstyle_length(integer, text);"
-		fi
-		# Last appeared in v13
-		if [ $oldpgversion -lt 140000 ]; then
-			fix_sql="$fix_sql
-				 DROP FUNCTION IF EXISTS
-					public.putenv(text);	-- last in v13
-				 DROP OPERATOR IF EXISTS	-- last in v13
-					public.#@# (pg_catalog.int8, NONE),
-					public.#%# (pg_catalog.int8, NONE),
-					public.!=- (pg_catalog.int8, NONE),
-					public.#@%# (pg_catalog.int8, NONE);"
-		fi
-		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
-
-		# WITH OIDS is not supported anymore in v12, so remove support
-		# for any relations marked as such.
-		if [ $oldpgversion -lt 120000 ]; then
-			fix_sql="DO \$stmt\$
-				DECLARE
-					rec text;
-				BEGIN
-				FOR rec in
-					SELECT oid::regclass::text
-					FROM pg_class
-					WHERE relname !~ '^pg_'
-						AND relhasoids
-						AND relkind in ('r','m')
-					ORDER BY 1
-				LOOP
-					execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';
-				END LOOP;
-				END; \$stmt\$;"
-			psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
-		fi
+		psql -X -d regression -f "test-upgrade.sql" || psql_fix_sql_status=$?
 
 		# Handling of --extra-float-digits gets messy after v12.
 		# Note that this changes the dumps from the old and new
-- 
2.17.0


--qD80nKKiJWXm4UaL
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v7-0002-wip-support-pg_upgrade-from-older-versions.patch"