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 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: Branch we are upgrading from, e.g. 'REL_11_STABLE' + +=item C: Object with a method C +to perform SQL against the old installation, returning psql's exit status + +=item C: 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, so that it will match the results of +C 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: Branch we are upgrading from, e.g. 'REL_11_STABLE' + +=item C: 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, so that it will match the old +dump output file as adjusted by C. + +Typically this involves deleting data not present in the old installation. + +Arguments: + +=over + +=item C: Branch we are upgrading from, e.g. 'REL_11_STABLE' + +=item C: 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;