adjustupgrade.patch

diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
new file mode 100644
index 0000000000..279b2bd0e6
--- /dev/null
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -0,0 +1,421 @@
+
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+=pod
+
+=head1 NAME
+
+PostgreSQL::Test::AdjustUpgrade - helper module for cross-version upgrade tests
+
+=head1 SYNOPSIS
+
+  use PostgreSQL::Test::AdjustUpgrade;
+
+  # Adjust contents of old-version database before dumping
+  adjust_database_contents($old_version, $psql, %dbnames);
+
+  # Adjust contents of old pg_dumpall output file to match newer version
+  $dump = adjust_old_dumpfile($old_version, $dump);
+
+  # Adjust contents of new pg_dumpall output file to match older version
+  $dump = adjust_new_dumpfile($old_version, $dump);
+
+=head1 DESCRIPTION
+
+C<PostgreSQL::Test::AdjustUpgrade> encapsulates various hacks needed to
+compare the results of cross-version upgrade tests.
+
+=cut
+
+package PostgreSQL::Test::AdjustUpgrade;
+
+use strict;
+use warnings;
+
+use Exporter 'import';
+
+our @EXPORT = qw(
+  adjust_database_contents
+  adjust_old_dumpfile
+  adjust_new_dumpfile
+);
+
+=pod
+
+=head1 ROUTINES
+
+=over
+
+=item adjust_database_contents($old_version, $psql, %dbnames)
+
+Perform any DDL operations against the old-version installation that are
+needed before we can pg_upgrade it into the current PostgreSQL version.
+
+Typically this involves dropping or adjusting no-longer-supported objects.
+
+Arguments:
+
+=over
+
+=item C<old_version>: Branch we are upgrading from, e.g. 'REL_11_STABLE'
+
+=item C<psql>: Object with a method C<old_psql($dbname, $sql_command)>
+to perform SQL against the old installation, returning psql's exit status
+
+=item C<dbnames>: Hash of database names present in the old installation
+
+=back
+
+=cut
+
+sub adjust_database_contents
+{
+	my ($old_version, $psql, %dbnames) = @_;
+
+	# nothing to do for non-cross-version tests
+	return if $old_version eq 'HEAD';
+
+	# stuff not supported from release 16
+	if (    $old_version ge 'REL_12_STABLE'
+		and $old_version lt 'REL_16_STABLE')
+	{
+		# Can't upgrade aclitem in user tables from pre 16 to 16+.
+		# Also can't handle child tables with locally-generated columns.
+		my $prstmt = join(';',
+			'alter table public.tab_core_types drop column aclitem',
+			'drop table public.gtest_normal_child',
+			'drop table public.gtest_normal_child2');
+
+		$psql->old_psql("regression", $prstmt);
+		return if $?;
+	}
+
+	# stuff not supported from release 14
+	if ($old_version lt 'REL_14_STABLE')
+	{
+		# postfix operators (some don't exist in very old versions)
+		my $prstmt = join(';',
+			'drop operator #@# (bigint,NONE)',
+			'drop operator #%# (bigint,NONE)',
+			'drop operator if exists !=- (bigint,NONE)',
+			'drop operator if exists #@%# (bigint,NONE)');
+
+		$psql->old_psql("regression", $prstmt);
+		return if $?;
+
+		# get rid of dblink's dependencies on regress.so
+		$prstmt = join(';',
+			'drop function if exists public.putenv(text)',
+			'drop function if exists public.wait_pid(integer)');
+
+		my $regrdb =
+		  $old_version le "REL9_4_STABLE"
+		  ? "contrib_regression"
+		  : "contrib_regression_dblink";
+
+		if ($dbnames{$regrdb})
+		{
+			$psql->old_psql($regrdb, $prstmt);
+			return if $?;
+		}
+	}
+
+	# user table OIDs are gone from release 12 on
+	if ($old_version lt 'REL_12_STABLE')
+	{
+		my $nooid_stmt = q{
+           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';
+                 RAISE NOTICE 'removing oids from table %', rec;
+              END LOOP;
+           END; $stmt$;
+        };
+
+		foreach my $oiddb ("regression", "contrib_regression_btree_gist")
+		{
+			next unless $dbnames{$oiddb};
+			$psql->old_psql($oiddb, $nooid_stmt);
+			return if $?;
+		}
+
+		# this one had OIDs too, but we'll just drop it
+		if (   $old_version ge 'REL_10_STABLE'
+			&& $dbnames{'contrib_regression_postgres_fdw'})
+		{
+			$psql->old_psql(
+				"contrib_regression_postgres_fdw",
+				"drop foreign table ft_pg_type");
+			return if $?;
+		}
+	}
+
+	# abstime+friends are gone from release 12 on; but these tables
+	# might or might not be present depending on regression test vintage
+	if ($old_version lt 'REL_12_STABLE')
+	{
+		$psql->old_psql("regression",
+			"drop table if exists abstime_tbl, reltime_tbl, tinterval_tbl");
+		return if $?;
+	}
+
+	# some regression functions gone from release 11 on
+	if ($old_version lt 'REL_11_STABLE')
+	{
+		my $prstmt = join(';',
+			'drop function if exists public.boxarea(box)',
+			'drop function if exists public.funny_dup17()');
+
+		$psql->old_psql("regression", $prstmt);
+		return if $?;
+	}
+
+	# version-0 C functions are no longer supported
+	if ($old_version lt 'REL_10_STABLE')
+	{
+		$psql->old_psql("regression",
+			"drop function oldstyle_length(integer, text)");
+		return if $?;
+	}
+
+	if ($old_version lt 'REL9_5_STABLE')
+	{
+		# changes of underlying functions
+		my $prstmt = join(';',
+			'drop operator @#@ (NONE, bigint)',
+			'CREATE OPERATOR @#@ ('
+			  . 'PROCEDURE = factorial, RIGHTARG = bigint )',
+			'drop aggregate public.array_cat_accum(anyarray)',
+			'CREATE AGGREGATE array_larger_accum (anyarray) ' . ' ( '
+			  . '   sfunc = array_larger, '
+			  . '   stype = anyarray, '
+			  . '   initcond = $${}$$ '
+			  . '  ) ');
+
+		$psql->old_psql("regression", $prstmt);
+		return if $?;
+
+		# "=>" is no longer valid as an operator name
+		$psql->old_psql("regression",
+			'drop operator if exists public.=> (bigint, NONE)');
+		return if $?;
+	}
+
+	# remove dbs of modules known to cause pg_upgrade to fail
+	# anything not builtin and incompatible should clean up its own db
+	foreach my $bad_module ("test_ddl_deparse", "tsearch2")
+	{
+		if ($dbnames{"contrib_regression_$bad_module"})
+		{
+			$psql->old_psql("postgres",
+				"drop database contrib_regression_$bad_module");
+			return if $?;
+		}
+	}
+
+	# dblink in 9.5 has permissions oddities, not worth fixing
+	if (   $old_version eq 'REL9_5_STABLE'
+		&& $dbnames{"contrib_regression_dblink"})
+	{
+		$psql->old_psql("postgres",
+			"drop database contrib_regression_dblink");
+		return if $?;
+	}
+
+	# avoid version number issues with test_ext7
+	if ($dbnames{contrib_regression_test_extensions})
+	{
+		$psql->old_psql(
+			"contrib_regression_test_extensions",
+			"drop extension if exists test_ext7");
+		return if $?;
+	}
+
+	return;
+}
+
+=pod
+
+=item adjust_old_dumpfile($old_version, $dump)
+
+Edit a dump output file, taken from the adjusted old-version installation
+by current-version C<pg_dumpall -s>, so that it will match the results of
+C<pg_dumpall -s> on the pg_upgrade'd installation.
+
+Typically this involves coping with cosmetic differences in the output
+of backend subroutines used by pg_dump.
+
+Arguments:
+
+=over
+
+=item C<old_version>: Branch we are upgrading from, e.g. 'REL_11_STABLE'
+
+=item C<dump>: Contents of dump file
+
+=back
+
+Returns the modified dump text.
+
+=cut
+
+sub adjust_old_dumpfile
+{
+	my ($old_version, $dump) = @_;
+
+	# nothing to do for non-cross-version tests
+	return $dump if $old_version eq 'HEAD';
+
+	# Version comments will certainly not match.
+	$dump =~ s/^-- Dumped from database version.*\n//mg;
+
+	if (    $old_version ge 'REL_14_STABLE'
+		and $old_version lt 'REL_16_STABLE')
+	{
+		# Fix up some privilege-set discrepancies.
+		$dump =~
+		  s/^REVOKE SELECT,INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLE/REVOKE ALL ON TABLE/mg;
+		$dump =~
+		  s/^GRANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE,UPDATE ON TABLE/GRANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE,MAINTAIN,UPDATE ON TABLE/mg;
+	}
+
+	if ($old_version lt 'REL_14_STABLE')
+	{
+		# Remove mentions of extended hash functions.
+		$dump =~
+		  s/^(\s+OPERATOR 1 =\(integer,integer\)) ,\n\s+FUNCTION 2 \(integer, integer\) public\.part_hashint4_noop\(integer,bigint\);/$1;/mg;
+		$dump =~
+		  s/^(\s+OPERATOR 1 =\(text,text\)) ,\n\s+FUNCTION 2 \(text, text\) public\.part_hashtext_length\(text,bigint\);/$1;/mg;
+	}
+
+	# Change trigger definitions to say ... EXECUTE FUNCTION ...
+	if ($old_version lt 'REL_12_STABLE')
+	{
+		# would like to use lookbehind here but perl complains
+		# so do it this way
+		$dump =~ s/
+			(^CREATE\sTRIGGER\s.*?)
+			\sEXECUTE\sPROCEDURE
+			/$1 EXECUTE FUNCTION/mgx;
+	}
+
+	if ($old_version lt 'REL9_6_STABLE')
+	{
+		# adjust some places where we don't print so many parens anymore
+		$dump =~
+		  s/^'New York'\tnew & york \| big & apple \| nyc\t'new' & 'york'\t\( 'new' & 'york' \| 'big' & 'appl' \) \| 'nyc'/'New York'\tnew & york | big & apple | nyc\t'new' & 'york'\t'new' & 'york' | 'big' & 'appl' | 'nyc'/mg;
+		$dump =~
+		  s/^'Sanct Peter'\tPeterburg \| peter \| 'Sanct Peterburg'\t'sanct' & 'peter'\t\( 'peterburg' \| 'peter' \) \| 'sanct' & 'peterburg'/'Sanct Peter'\tPeterburg | peter | 'Sanct Peterburg'\t'sanct' & 'peter'\t'peterburg' | 'peter' | 'sanct' & 'peterburg'/mg;
+	}
+
+	if ($old_version lt 'REL9_5_STABLE')
+	{
+		# adjust some places where we don't print so many parens anymore
+		$dump =~
+		  s/CONSTRAINT sequence_con CHECK \(\(\(\(x > 3\) AND \(y <> 'check failed'::text\)\) AND \(z < 8\)\)\)/CONSTRAINT sequence_con CHECK (((x > 3) AND (y <> 'check failed'::text) AND (z < 8)))/mg;
+		$dump =~
+		  s/CONSTRAINT copy_con CHECK \(\(\(\(x > 3\) AND \(y <> 'check failed'::text\)\) AND \(x < 7\)\)\)/CONSTRAINT copy_con CHECK (((x > 3) AND (y <> 'check failed'::text) AND (x < 7)))/mg;
+		$dump =~
+		  s/CONSTRAINT insert_con CHECK \(\(\(\(x >= 3\) AND \(y <> 'check failed'::text\)\) AND \(x < 8\)\)\)/CONSTRAINT insert_con CHECK (((x >= 3) AND (y <> 'check failed'::text) AND (x < 8)))/mg;
+		$dump =~
+		  s/DEFAULT \(\(-1\) \* currval\('public\.insert_seq'::regclass\)\)/DEFAULT ('-1'::integer * currval('public.insert_seq'::regclass))/mg;
+		$dump =~
+		  s/WHERE \(\(\(rsl\.sl_color = rsh\.slcolor\) AND \(rsl\.sl_len_cm >= rsh\.slminlen_cm\)\) AND \(rsl\.sl_len_cm <= rsh\.slmaxlen_cm\)\)/WHERE ((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm))/mg;
+		$dump =~
+		  s/WHERE \(\(\(rule_and_refint_t3\.id3a = new\.id3a\) AND \(rule_and_refint_t3\.id3b = new\.id3b\)\) AND \(rule_and_refint_t3\.id3c = new\.id3c\)\)/WHERE ((rule_and_refint_t3.id3a = new.id3a) AND (rule_and_refint_t3.id3b = new.id3b) AND (rule_and_refint_t3.id3c = new.id3c))/mg;
+		$dump =~
+		  s/WHERE \(\(\(rule_and_refint_t3_1\.id3a = new\.id3a\) AND \(rule_and_refint_t3_1\.id3b = new\.id3b\)\) AND \(rule_and_refint_t3_1\.id3c = new\.id3c\)\)/WHERE ((rule_and_refint_t3_1.id3a = new.id3a) AND (rule_and_refint_t3_1.id3b = new.id3b) AND (rule_and_refint_t3_1.id3c = new.id3c))/mg;
+	}
+
+	# Suppress blank lines, as some places in pg_dump emit more or fewer.
+	$dump =~ s/\n\n+/\n/g;
+
+	return $dump;
+}
+
+=pod
+
+=item adjust_new_dumpfile($old_version, $dump)
+
+Edit a dump output file, taken from the pg_upgrade'd installation
+by current-version C<pg_dumpall -s>, so that it will match the old
+dump output file as adjusted by C<adjust_old_dumpfile>.
+
+Typically this involves deleting data not present in the old installation.
+
+Arguments:
+
+=over
+
+=item C<old_version>: Branch we are upgrading from, e.g. 'REL_11_STABLE'
+
+=item C<dump>: Contents of dump file
+
+=back
+
+Returns the modified dump text.
+
+=cut
+
+sub adjust_new_dumpfile
+{
+	my ($old_version, $dump) = @_;
+
+	# nothing to do for non-cross-version tests
+	return $dump if $old_version eq 'HEAD';
+
+	# Version comments will certainly not match.
+	$dump =~ s/^-- Dumped from database version.*\n//mg;
+
+	if ($old_version lt 'REL_14_STABLE')
+	{
+		# Suppress noise-word uses of IN in CREATE/ALTER PROCEDURE.
+		$dump =~ s/^(CREATE PROCEDURE .*?)\(IN /$1(/mg;
+		$dump =~ s/^(ALTER PROCEDURE .*?)\(IN /$1(/mg;
+		$dump =~ s/^(CREATE PROCEDURE .*?), IN /$1, /mg;
+		$dump =~ s/^(ALTER PROCEDURE .*?), IN /$1, /mg;
+		$dump =~ s/^(CREATE PROCEDURE .*?), IN /$1, /mg;
+		$dump =~ s/^(ALTER PROCEDURE .*?), IN /$1, /mg;
+
+		# Remove SUBSCRIPT clauses in CREATE TYPE.
+		$dump =~ s/^\s+SUBSCRIPT = raw_array_subscript_handler,\n//mg;
+
+		# Remove multirange_type_name clauses in CREATE TYPE AS RANGE.
+		$dump =~ s/,\n\s+multirange_type_name = .*?(,?)$/$1/mg;
+
+		# Remove mentions of extended hash functions.
+		$dump =~
+		  s/^ALTER OPERATOR FAMILY public\.part_test_int4_ops USING hash ADD\n\s+FUNCTION 2 \(integer, integer\) public\.part_hashint4_noop\(integer,bigint\);//mg;
+		$dump =~
+		  s/^ALTER OPERATOR FAMILY public\.part_test_text_ops USING hash ADD\n\s+FUNCTION 2 \(text, text\) public\.part_hashtext_length\(text,bigint\);//mg;
+	}
+
+	# pre-v12 dumps will not say anything about default_table_access_method.
+	if ($old_version lt 'REL_12_STABLE')
+	{
+		$dump =~ s/^SET default_table_access_method = heap;\n//mg;
+	}
+
+	# Suppress blank lines, as some places in pg_dump emit more or fewer.
+	$dump =~ s/\n\n+/\n/g;
+
+	return $dump;
+}
+
+=pod
+
+=back
+
+=cut
+
+1;