Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pGmn8-0003sa-OG for pgsql-hackers@arkaria.postgresql.org; Sat, 14 Jan 2023 20:06:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pGmn7-0000K7-AR for pgsql-hackers@arkaria.postgresql.org; Sat, 14 Jan 2023 20:06:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pGmn6-0000Jx-P8 for pgsql-hackers@lists.postgresql.org; Sat, 14 Jan 2023 20:06:13 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pGmn3-0003fq-AD for pgsql-hackers@lists.postgresql.org; Sat, 14 Jan 2023 20:06:11 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 30EK662q1199697; Sat, 14 Jan 2023 15:06:06 -0500 From: Tom Lane To: Andrew Dunstan cc: Michael Paquier , pgsql-hackers@lists.postgresql.org Subject: Re: Extracting cross-version-upgrade knowledge from buildfarm client In-reply-to: <1004356.1673711255@sss.pgh.pa.us> References: <891521.1673657296@sss.pgh.pa.us> <700055c2-e5e9-6d00-09cf-5b9666a1d17b@dunslane.net> <1004356.1673711255@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sat, 14 Jan 2023 10:47:35 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1199648.1673726761.0@sss.pgh.pa.us> Date: Sat, 14 Jan 2023 15:06:06 -0500 Message-ID: <1199696.1673726766@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1199648.1673726761.1@sss.pgh.pa.us> I wrote: > OK, I'll take a look at that and make a new draft. Here's version 2, incorporating your suggestions and with some further work to make it handle 9.2 fully. I think this could be committable so far as HEAD is concerned, though I still need to make versions of AdjustUpgrade.pm for the back branches. I tried to use this to replace upgrade_adapt.sql, but failed so far because I couldn't figure out exactly how you're supposed to use 002_pg_upgrade.pl with an old source installation. It's not terribly well documented. In any case I think we need a bit more thought about that, because it looks like 002_pg_upgrade.pl thinks that you can supply any random dump file to serve as the initial state of the old installation; but neither what I have here nor any likely contents of upgrade_adapt.sql or the "custom filter" rules are going to work on databases that aren't just the standard regression database(s) of the old version. I assume we should plan on reverting 9814ff550 (Add custom filtering rules to the TAP tests of pg_upgrade)? Does that have any plausible use that's not superseded by this patchset? regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="adjustupgrade-2.patch"; charset="us-ascii" Content-ID: <1199648.1673726761.2@sss.pgh.pa.us> Content-Description: adjustupgrade-2.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/per= l/PostgreSQL/Test/AdjustUpgrade.pm new file mode 100644 index 0000000000..622f649b05 --- /dev/null +++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm @@ -0,0 +1,500 @@ + +# Copyright (c) 2023, PostgreSQL Global Development Group + +=3Dpod + +=3Dhead1 NAME + +PostgreSQL::Test::AdjustUpgrade - helper module for cross-version upgrade= tests + +=3Dhead1 SYNOPSIS + + use PostgreSQL::Test::AdjustUpgrade; + + # Build commands to adjust contents of old-version database before dump= ing + $statements =3D adjust_database_contents($old_version, %dbnames); + + # Adjust contents of old pg_dumpall output file to match newer version + $dump =3D adjust_old_dumpfile($old_version, $dump); + + # Adjust contents of new pg_dumpall output file to match older version + $dump =3D adjust_new_dumpfile($old_version, $dump); + +=3Dhead1 DESCRIPTION + +C encapsulates various hacks needed to +compare the results of cross-version upgrade tests. + +=3Dcut + +package PostgreSQL::Test::AdjustUpgrade; + +use strict; +use warnings; + +use Exporter 'import'; +use PostgreSQL::Version; + +our @EXPORT =3D qw( + adjust_database_contents + adjust_old_dumpfile + adjust_new_dumpfile +); + +=3Dpod + +=3Dhead1 ROUTINES + +=3Dover + +=3Ditem $statements =3D adjust_database_contents($old_version, %dbnames) + +Generate SQL commands to perform any changes to an old-version installati= on +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: + +=3Dover + +=3Ditem C: Branch we are upgrading from. This can be a bran= ch +name such as 'HEAD' or 'REL_11_STABLE', but it can also be any string +that PostgreSQL::Version accepts. + +=3Ditem C: Hash of database names present in the old installatio= n. + +=3Dback + +Returns a reference to a hash, wherein the keys are database names and th= e +values are arrayrefs to lists of statements to be run in those databases. + +=3Dcut + +sub adjust_database_contents +{ + my ($old_version, %dbnames) =3D @_; + my $result =3D {}; + + # nothing to do for non-cross-version tests + return $result if $old_version eq 'HEAD'; + + # convert branch name to numeric form + $old_version =3D~ s/REL_?(\d+(?:_\d+)?)_STABLE/$1/; + $old_version =3D~ s/_/./; + $old_version =3D PostgreSQL::Version->new($old_version); + + # 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"}) + { + _add_st($result, 'postgres', + "drop database contrib_regression_$bad_module"); + delete($dbnames{"contrib_regression_$bad_module"}); + } + } + + # avoid version number issues with test_ext7 + if ($dbnames{contrib_regression_test_extensions}) + { + _add_st( + $result, + 'contrib_regression_test_extensions', + 'drop extension if exists test_ext7'); + } + + # stuff not supported from release 16 + if ($old_version >=3D 12 && $old_version < 16) + { + # Can't upgrade aclitem in user tables from pre 16 to 16+. + _add_st($result, 'regression', + 'alter table public.tab_core_types drop column aclitem'); + # Can't handle child tables with locally-generated columns. + _add_st( + $result, 'regression', + 'drop table public.gtest_normal_child', + 'drop table public.gtest_normal_child2'); + } + + # stuff not supported from release 14 + if ($old_version < 14) + { + # postfix operators (some don't exist in very old versions) + _add_st( + $result, + 'regression', + 'drop operator #@# (bigint,NONE)', + 'drop operator #%# (bigint,NONE)', + 'drop operator if exists !=3D- (bigint,NONE)', + 'drop operator if exists #@%# (bigint,NONE)'); + + # get rid of dblink's dependencies on regress.so + my $regrdb =3D + $old_version le '9.4' + ? 'contrib_regression' + : 'contrib_regression_dblink'; + + if ($dbnames{$regrdb}) + { + _add_st( + $result, $regrdb, + 'drop function if exists public.putenv(text)', + 'drop function if exists public.wait_pid(integer)'); + } + } + + # user table OIDs are gone from release 12 on + if ($old_version < 12) + { + my $nooid_stmt =3D 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}; + _add_st($result, $oiddb, $nooid_stmt); + } + + # this table had OIDs too, but we'll just drop it + if ($old_version >=3D 10 && $dbnames{'contrib_regression_postgres_fdw'}= ) + { + _add_st( + $result, + 'contrib_regression_postgres_fdw', + 'drop foreign table ft_pg_type'); + } + } + + # 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 < 12) + { + _add_st($result, 'regression', + 'drop table if exists abstime_tbl, reltime_tbl, tinterval_tbl'); + } + + # some regression functions gone from release 11 on + if ($old_version < 11) + { + _add_st( + $result, 'regression', + 'drop function if exists public.boxarea(box)', + 'drop function if exists public.funny_dup17()'); + } + + # version-0 C functions are no longer supported + if ($old_version < 10) + { + _add_st($result, 'regression', + 'drop function oldstyle_length(integer, text)'); + } + + if ($old_version lt '9.5') + { + # cope with changes of underlying functions + _add_st( + $result, + 'regression', + 'drop operator @#@ (NONE, bigint)', + 'CREATE OPERATOR @#@ (' + . 'PROCEDURE =3D factorial, RIGHTARG =3D bigint )', + 'drop aggregate public.array_cat_accum(anyarray)', + 'CREATE AGGREGATE array_larger_accum (anyarray) ' . ' ( ' + . ' sfunc =3D array_larger, ' + . ' stype =3D anyarray, ' + . ' initcond =3D $${}$$ ' . ' ) '); + + # "=3D>" is no longer valid as an operator name + _add_st($result, 'regression', + 'drop operator if exists public.=3D> (bigint, NONE)'); + } + + return $result; +} + +# Internal subroutine to add statement(s) to the list for the given db. +sub _add_st +{ + my ($result, $db, @st) =3D @_; + + $result->{$db} ||=3D []; + push(@{ $result->{$db} }, @st); +} + +=3Dpod + +=3Ditem 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: + +=3Dover + +=3Ditem C: Branch we are upgrading from. This can be a bran= ch +name such as 'HEAD' or 'REL_11_STABLE', but it can also be any string +that PostgreSQL::Version accepts. + +=3Ditem C: Contents of dump file + +=3Dback + +Returns the modified dump text. + +=3Dcut + +sub adjust_old_dumpfile +{ + my ($old_version, $dump) =3D @_; + + # nothing to do for non-cross-version tests + return $dump if $old_version eq 'HEAD'; + + # convert branch name to numeric form + $old_version =3D~ s/REL_?(\d+(?:_\d+)?)_STABLE/$1/; + $old_version =3D~ s/_/./; + $old_version =3D PostgreSQL::Version->new($old_version); + + # Version comments will certainly not match. + $dump =3D~ s/^-- Dumped from database version.*\n//mg; + + if ($old_version >=3D 14 && $old_version < 16) + { + # Fix up some privilege-set discrepancies. + $dump =3D~ + s/^REVOKE SELECT,INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON = TABLE/REVOKE ALL ON TABLE/mg; + $dump =3D~ + s/^GRANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE,UPDATE ON TABLE/GR= ANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE,MAINTAIN,UPDATE ON TABLE/mg; + } + + if ($old_version < 14) + { + # Remove mentions of extended hash functions. + $dump =3D~ + s/^(\s+OPERATOR 1 =3D\(integer,integer\)) ,\n\s+FUNCTION 2 \(integer,= integer\) public\.part_hashint4_noop\(integer,bigint\);/$1;/mg; + $dump =3D~ + s/^(\s+OPERATOR 1 =3D\(text,text\)) ,\n\s+FUNCTION 2 \(text, text\) p= ublic\.part_hashtext_length\(text,bigint\);/$1;/mg; + } + + # Change trigger definitions to say ... EXECUTE FUNCTION ... + if ($old_version < 12) + { + # would like to use lookbehind here but perl complains + # so do it this way + $dump =3D~ s/ + (^CREATE\sTRIGGER\s.*?) + \sEXECUTE\sPROCEDURE + /$1 EXECUTE FUNCTION/mgx; + } + + if ($old_version lt '9.6') + { + # adjust some places where we don't print so many parens anymore + $dump =3D~ + 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 =3D~ + s/^'Sanct Peter'\tPeterburg \| peter \| 'Sanct Peterburg'\t'sanct' & = 'peter'\t\( 'peterburg' \| 'peter' \) \| 'sanct' & 'peterburg'/'Sanct Pete= r'\tPeterburg | peter | 'Sanct Peterburg'\t'sanct' & 'peter'\t'peterburg' = | 'peter' | 'sanct' & 'peterburg'/mg; + } + + if ($old_version lt '9.5') + { + # adjust some places where we don't print so many parens anymore + $dump =3D~ + s/CONSTRAINT sequence_con CHECK \(\(\(\(x > 3\) AND \(y <> 'check fai= led'::text\)\) AND \(z < 8\)\)\)/CONSTRAINT sequence_con CHECK (((x > 3) A= ND (y <> 'check failed'::text) AND (z < 8)))/mg; + $dump =3D~ + 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 =3D~ + s/CONSTRAINT insert_con CHECK \(\(\(\(x >=3D 3\) AND \(y <> 'check fa= iled'::text\)\) AND \(x < 8\)\)\)/CONSTRAINT insert_con CHECK (((x >=3D 3)= AND (y <> 'check failed'::text) AND (x < 8)))/mg; + $dump =3D~ + s/DEFAULT \(\(-1\) \* currval\('public\.insert_seq'::regclass\)\)/DEF= AULT ('-1'::integer * currval('public.insert_seq'::regclass))/mg; + $dump =3D~ + s/WHERE \(\(\(rsl\.sl_color =3D rsh\.slcolor\) AND \(rsl\.sl_len_cm >= =3D rsh\.slminlen_cm\)\) AND \(rsl\.sl_len_cm <=3D rsh\.slmaxlen_cm\)\)/WH= ERE ((rsl.sl_color =3D rsh.slcolor) AND (rsl.sl_len_cm >=3D rsh.slminlen_c= m) AND (rsl.sl_len_cm <=3D rsh.slmaxlen_cm))/mg; + $dump =3D~ + s/WHERE \(\(\(rule_and_refint_t3\.id3a =3D new\.id3a\) AND \(rule_and= _refint_t3\.id3b =3D new\.id3b\)\) AND \(rule_and_refint_t3\.id3c =3D new\= .id3c\)\)/WHERE ((rule_and_refint_t3.id3a =3D new.id3a) AND (rule_and_refi= nt_t3.id3b =3D new.id3b) AND (rule_and_refint_t3.id3c =3D new.id3c))/mg; + $dump =3D~ + s/WHERE \(\(\(rule_and_refint_t3_1\.id3a =3D new\.id3a\) AND \(rule_a= nd_refint_t3_1\.id3b =3D new\.id3b\)\) AND \(rule_and_refint_t3_1\.id3c =3D= new\.id3c\)\)/WHERE ((rule_and_refint_t3_1.id3a =3D new.id3a) AND (rule_a= nd_refint_t3_1.id3b =3D new.id3b) AND (rule_and_refint_t3_1.id3c =3D new.i= d3c))/mg; + } + + if ($old_version lt '9.3') + { + # CREATE VIEW/RULE statements were not pretty-printed before 9.3. + # To cope, reduce all whitespace sequences within them to one space. + # This must be done on both old and new dumps. + $dump =3D _mash_view_whitespace($dump); + + # _mash_view_whitespace doesn't handle multi-command rules; + # rather than trying to fix that, just hack the exceptions manually. + $dump =3D~ + s/CREATE RULE rtest_sys_del AS ON DELETE TO public\.rtest_system DO \= (DELETE FROM public\.rtest_interface WHERE \(rtest_interface\.sysname =3D = old\.sysname\); DELETE FROM public\.rtest_admin WHERE \(rtest_admin\.sysna= me =3D old\.sysname\); \);/CREATE RULE rtest_sys_del AS ON DELETE TO publi= c.rtest_system DO (DELETE FROM public.rtest_interface WHERE (rtest_interfa= ce.sysname =3D old.sysname);\n DELETE FROM public.rtest_admin\n WHERE (rt= est_admin.sysname =3D old.sysname);\n);/m; + $dump =3D~ + s/CREATE RULE rtest_sys_upd AS ON UPDATE TO public\.rtest_system DO \= (UPDATE public\.rtest_interface SET sysname =3D new\.sysname WHERE \(rtest= _interface\.sysname =3D old\.sysname\); UPDATE public\.rtest_admin SET sys= name =3D new\.sysname WHERE \(rtest_admin\.sysname =3D old\.sysname\); \);= /CREATE RULE rtest_sys_upd AS ON UPDATE TO public.rtest_system DO (UPDATE = public.rtest_interface SET sysname =3D new.sysname WHERE (rtest_interface.= sysname =3D old.sysname);\n UPDATE public.rtest_admin SET sysname =3D new.= sysname\n WHERE (rtest_admin.sysname =3D old.sysname);\n);/m; + + # and there's one place where pre-9.3 uses more aliases than we do now + $dump =3D~ + s/CREATE RULE rule_and_refint_t3_ins AS ON INSERT TO public\.rule_and= _refint_t3 WHERE \(EXISTS \(SELECT 1 FROM public\.rule_and_refint_t3 WHERE= \(\(rule_and_refint_t3\.id3a =3D new\.id3a\) AND \(rule_and_refint_t3\.id= 3b =3D new\.id3b\) AND \(rule_and_refint_t3\.id3c =3D new\.id3c\)\)\)\) DO= INSTEAD UPDATE public\.rule_and_refint_t3 SET data =3D new\.data WHERE \(= \(rule_and_refint_t3\.id3a =3D new\.id3a\) AND \(rule_and_refint_t3\.id3b = =3D new\.id3b\) AND \(rule_and_refint_t3\.id3c =3D new\.id3c\)\);/CREATE R= ULE rule_and_refint_t3_ins AS ON INSERT TO public.rule_and_refint_t3 WHERE= (EXISTS (SELECT 1 FROM public.rule_and_refint_t3 rule_and_refint_t3_1 WHE= RE ((rule_and_refint_t3_1.id3a =3D new.id3a) AND (rule_and_refint_t3_1.id3= b =3D new.id3b) AND (rule_and_refint_t3_1.id3c =3D new.id3c)))) DO INSTEAD= UPDATE public.rule_and_refint_t3 SET data =3D new.data WHERE ((rule_and_r= efint_t3.id3a =3D new.id3a) AND (rule_and_refint_t3.id3b =3D new.id3b) AND= (rule_and_refint_t3.id3c =3D new.id3c));/m; + + # Also fix old use of NATURAL JOIN syntax + $dump =3D~ + s/NATURAL JOIN public\.credit_card r/JOIN public.credit_card r USING = (cid)/mg; + $dump =3D~ + s/NATURAL JOIN public\.credit_usage r/JOIN public.credit_usage r USIN= G (cid)/mg; + } + + # Suppress blank lines, as some places in pg_dump emit more or fewer. + $dump =3D~ s/\n\n+/\n/g; + + return $dump; +} + +# Internal subroutine to mangle whitespace within view/rule commands. +# Any consecutive sequence of whitespace is reduced to one space. +sub _mash_view_whitespace +{ + my ($dump) =3D @_; + + foreach my $leader ('CREATE VIEW', 'CREATE RULE') + { + my @splitchunks =3D split $leader, $dump; + + $dump =3D shift(@splitchunks); + foreach my $chunk (@splitchunks) + { + my @thischunks =3D split /;/, $chunk, 2; + my $stmt =3D shift(@thischunks); + + # now $stmt is just the body of the CREATE VIEW/RULE + $stmt =3D~ s/\s+/ /sg; + # we also need to smash these forms for sub-selects and rules + $stmt =3D~ s/\( SELECT/(SELECT/g; + $stmt =3D~ s/\( INSERT/(INSERT/g; + $stmt =3D~ s/\( UPDATE/(UPDATE/g; + $stmt =3D~ s/\( DELETE/(DELETE/g; + + $dump .=3D $leader . $stmt . ';' . $thischunks[0]; + } + } + return $dump; +} + +=3Dpod + +=3Ditem 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: + +=3Dover + +=3Ditem C: Branch we are upgrading from. This can be a bran= ch +name such as 'HEAD' or 'REL_11_STABLE', but it can also be any string +that PostgreSQL::Version accepts. + +=3Ditem C: Contents of dump file + +=3Dback + +Returns the modified dump text. + +=3Dcut + +sub adjust_new_dumpfile +{ + my ($old_version, $dump) =3D @_; + + # nothing to do for non-cross-version tests + return $dump if $old_version eq 'HEAD'; + + # convert branch name to numeric form + $old_version =3D~ s/REL_?(\d+(?:_\d+)?)_STABLE/$1/; + $old_version =3D~ s/_/./; + $old_version =3D PostgreSQL::Version->new($old_version); + + # Version comments will certainly not match. + $dump =3D~ s/^-- Dumped from database version.*\n//mg; + + if ($old_version < 14) + { + # Suppress noise-word uses of IN in CREATE/ALTER PROCEDURE. + $dump =3D~ s/^(CREATE PROCEDURE .*?)\(IN /$1(/mg; + $dump =3D~ s/^(ALTER PROCEDURE .*?)\(IN /$1(/mg; + $dump =3D~ s/^(CREATE PROCEDURE .*?), IN /$1, /mg; + $dump =3D~ s/^(ALTER PROCEDURE .*?), IN /$1, /mg; + $dump =3D~ s/^(CREATE PROCEDURE .*?), IN /$1, /mg; + $dump =3D~ s/^(ALTER PROCEDURE .*?), IN /$1, /mg; + + # Remove SUBSCRIPT clauses in CREATE TYPE. + $dump =3D~ s/^\s+SUBSCRIPT =3D raw_array_subscript_handler,\n//mg; + + # Remove multirange_type_name clauses in CREATE TYPE AS RANGE. + $dump =3D~ s/,\n\s+multirange_type_name =3D .*?(,?)$/$1/mg; + + # Remove mentions of extended hash functions. + $dump =3D~ + s/^ALTER OPERATOR FAMILY public\.part_test_int4_ops USING hash ADD\n\= s+FUNCTION 2 \(integer, integer\) public\.part_hashint4_noop\(integer,bigi= nt\);//mg; + $dump =3D~ + 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 < 12) + { + $dump =3D~ s/^SET default_table_access_method =3D heap;\n//mg; + } + + # dumps from pre-9.6 dblink may include redundant ACL settings + if ($old_version lt '9.6') + { + $dump =3D~ + s/^--\n-- Name: FUNCTION dblink_connect_u\(text\); Type: ACL; Schema:= public; Owner: .*\n--\n+REVOKE ALL ON FUNCTION public\.dblink_connect_u\(= text\) FROM PUBLIC;\n+--\n-- Name: FUNCTION dblink_connect_u\(text, text\)= ; Type: ACL; Schema: public; Owner: .*\n--\n+REVOKE ALL ON FUNCTION public= \.dblink_connect_u\(text, text\) FROM PUBLIC;\n//mg; + } + + if ($old_version lt '9.3') + { + # CREATE VIEW/RULE statements were not pretty-printed before 9.3. + # To cope, reduce all whitespace sequences within them to one space. + # This must be done on both old and new dumps. + $dump =3D _mash_view_whitespace($dump); + } + + # Suppress blank lines, as some places in pg_dump emit more or fewer. + $dump =3D~ s/\n\n+/\n/g; + + return $dump; +} + +=3Dpod + +=3Dback + +=3Dcut + +1; ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="xversion-2.patch"; charset="us-ascii" Content-ID: <1199648.1673726761.3@sss.pgh.pa.us> Content-Description: xversion-2.patch Content-Transfer-Encoding: quoted-printable diff -pudr client-code-REL_16.orig/PGBuild/Modules/TestUpgradeXversion.pm = client-code-REL_16/PGBuild/Modules/TestUpgradeXversion.pm --- client-code-REL_16.orig/PGBuild/Modules/TestUpgradeXversion.pm 2023-01= -13 12:20:51.000000000 -0500 +++ client-code-REL_16/PGBuild/Modules/TestUpgradeXversion.pm 2023-01-14 1= 4:23:27.120834037 -0500 @@ -323,31 +323,6 @@ sub save_for_testing return if $?; } = - if ($this_branch ne 'HEAD' && $this_branch le 'REL9_4_STABLE') - { - my $opsql =3D 'drop operator if exists public.=3D> (bigint, NONE)'; - - # syntax is illegal in 9.5 and later, and it shouldn't - # be possible for it to exist there anyway. - # quoting the operator can also fail, so it's left unquoted. - run_psql("$installdir/bin/psql", "-e", $opsql, "regression", - "$upgrade_loc/fix.log", 1); - return if $?; - } - - # remove dbs of modules known to cause pg_upgrade to fail - # anything not builtin and incompatible should clean up its own db - # e.g. jsonb_set_lax - - foreach my $bad_module ("test_ddl_deparse") - { - my $dsql =3D "drop database if exists contrib_regression_$bad_module"; - - run_psql("$installdir/bin/psql", "-e", $dsql, - "postgres", "$upgrade_loc/fix.log", 1); - return if $?; - } - # use a different logfile here to get around windows sharing issue system( qq{"$installdir/bin/pg_ctl" -D "$installdir/data-C" -w stop } . qq{>> "$upgrade_loc/ctl2.log" 2>&1}); @@ -375,6 +350,12 @@ sub test_upgrade ## no critic (Subrou print time_str(), "checking upgrade from $oversion to $this_branch ...\n= " if $verbose; = + # load helper module from source tree + unshift(@INC, "$self->{pgsql}/src/test/perl"); + require PostgreSQL::Test::AdjustUpgrade; + PostgreSQL::Test::AdjustUpgrade->import; + shift(@INC); + rmtree "$other_branch/inst/$upgrade_test"; copydir( "$other_branch/inst/data-C", @@ -414,6 +395,7 @@ sub test_upgrade ## no critic (Subrou = return if $?; = + # collect names of databases present in old installation. my $sql =3D 'select datname from pg_database'; = run_psql("psql", "-A -t", $sql, "postgres", @@ -425,186 +407,19 @@ sub test_upgrade ## no critic (Subrou do { s/\r$//; $dbnames{$_} =3D 1; } foreach @dbnames; = - if ($this_branch gt 'REL9_6_STABLE' || $this_branch eq 'HEAD') - { - run_psql( - "$other_branch/inst/bin/psql", "-e", - "drop database if exists contrib_regression_tsearch2", "postgres", - "$upgrade_loc/$oversion-copy.log", 1 - ); - return if $?; - - run_psql( - "$other_branch/inst/bin/psql", - "-e", - "drop function if exists oldstyle_length(integer, text)", - "regression", - "$upgrade_loc/$oversion-copy.log", - 1 - ); - return if $?; - } - - # some regression functions gone from release 11 on - if ( ($this_branch ge 'REL_11_STABLE' || $this_branch eq 'HEAD') - && ($oversion lt 'REL_11_STABLE' && $oversion ne 'HEAD')) - { - my $missing_funcs =3D q{drop function if exists public.boxarea(box); - drop function if exists public.funny_dup17(= ); - }; - $missing_funcs =3D~ s/\n//g; - - run_psql("$other_branch/inst/bin/psql", "-e", $missing_funcs, - "regression", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - } - - # avoid version number issues with test_ext7 - if ($dbnames{contrib_regression_test_extensions}) - { - my $noext7 =3D "drop extension if exists test_ext7"; - run_psql( - "$other_branch/inst/bin/psql", "-e", $noext7, - "contrib_regression_test_extensions", - "$upgrade_loc/$oversion-copy.log", 1 - ); - return if $?; - } - - # user table OIDS and abstime+friends are gone from release 12 on - if ( ($this_branch gt 'REL_11_STABLE' || $this_branch eq 'HEAD') - && ($oversion le 'REL_11_STABLE' && $oversion ne 'HEAD')) - { - my $nooid_stmt =3D 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}; - run_psql("$other_branch/inst/bin/psql", "-e", $nooid_stmt, - "$oiddb", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - } - - if ( $oversion ge 'REL_10_STABLE' - && $dbnames{'contrib_regression_postgres_fdw'}) - { - run_psql( - "$other_branch/inst/bin/psql", - "-e", - "drop foreign table if exists ft_pg_type", - "contrib_regression_postgres_fdw", - "$upgrade_loc/$oversion-copy.log", - 1 - ); - return if $?; - } - - if ($oversion lt 'REL9_3_STABLE') - { - run_psql( - "$other_branch/inst/bin/psql", - "-e", - "drop table if exists abstime_tbl, reltime_tbl, tinterval_tbl", - "regression", - "$upgrade_loc/$oversion-copy.log", - 1 - ); - return if $?; - } - } - - # stuff not supported from release 14 - if ( ($this_branch gt 'REL_13_STABLE' || $this_branch eq 'HEAD') - && ($oversion le 'REL_13_STABLE' && $oversion ne 'HEAD')) - { - my $prstmt =3D join(';', - 'drop operator if exists #@# (bigint,NONE)', - 'drop operator if exists #%# (bigint,NONE)', - 'drop operator if exists !=3D- (bigint,NONE)', - 'drop operator if exists #@%# (bigint,NONE)'); - - run_psql("$other_branch/inst/bin/psql", "-e", $prstmt, - "regression", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - - $prstmt =3D "drop function if exists public.putenv(text)"; - - my $regrdb =3D - $oversion le "REL9_4_STABLE" - ? "contrib_regression" - : "contrib_regression_dblink"; - - if ($dbnames{$regrdb}) - { - run_psql("$other_branch/inst/bin/psql", "-e", $prstmt, - "$regrdb", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - } - - if ($oversion le 'REL9_4_STABLE') - { - # this is fixed in 9.5 and later - $prstmt =3D join(';', - 'drop operator @#@ (NONE, bigint)', - 'CREATE OPERATOR @#@ (' - . 'PROCEDURE =3D factorial, ' - . 'RIGHTARG =3D bigint )'); - run_psql("$other_branch/inst/bin/psql", "-e", $prstmt, - "regression", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - } - - if ($oversion le 'REL9_4_STABLE') - { - # this is fixed in 9.5 and later - $prstmt =3D join(';', - 'drop aggregate if exists public.array_cat_accum(anyarray)', - 'CREATE AGGREGATE array_larger_accum (anyarray) ' . ' ( ' - . ' sfunc =3D array_larger, ' - . ' stype =3D anyarray, ' - . ' initcond =3D $${}$$ ' - . ' ) '); - run_psql("$other_branch/inst/bin/psql", "-e", $prstmt, - "regression", "$upgrade_loc/$oversion-copy.log", 1); - return if $?; - } - } + # obtain and execute commands needed to make old database upgradable. + my $adjust_cmds =3D adjust_database_contents($oversion, %dbnames); = - # stuff not supported from release 16 - if ( ($this_branch gt 'REL_15_STABLE' || $this_branch eq 'HEAD') - && ($oversion le 'REL_15_STABLE' && $oversion ne 'HEAD')) + foreach my $updb (keys %$adjust_cmds) { - # Can't upgrade aclitem in user tables from pre 16 to 16+. - # Also can't handle child tables with newly-generated columns. - my $prstmt =3D join( - ';', - 'alter table if exists public.tab_core_types - drop column if exists aclitem', - 'drop table if exists public.gtest_normal_child', - 'drop table if exists public.gtest_normal_child2' - ); + my $upcmds =3D join(";\n", @{ $adjust_cmds->{$updb} }); = - run_psql("$other_branch/inst/bin/psql", "-e", $prstmt, - "regression", "$upgrade_loc/$oversion-copy.log", 1); + run_psql("$other_branch/inst/bin/psql", "-e -v ON_ERROR_STOP=3D1", + $upcmds, $updb, "$upgrade_loc/$oversion-fix.log", 1); return if $?; } = + # perform a dump from the old database for comparison purposes. my $extra_digits =3D ""; = if ( $oversion ne 'HEAD' @@ -793,28 +608,27 @@ sub test_upgrade ## no critic (Subrou return if $?; } = - foreach my $dump ("$upgrade_loc/origin-$oversion.sql", - "$upgrade_loc/converted-$oversion-to-$this_branch.sql") - { - # Change trigger definitions to say ... EXECUTE FUNCTION ... + my $olddumpfile =3D "$upgrade_loc/origin-$oversion.sql"; + my $dump =3D file_contents($olddumpfile); = - my $contents =3D file_contents($dump); + $dump =3D adjust_old_dumpfile($oversion, $dump); = - # would like to use lookbehind here but perl complains - # so do it this way - $contents =3D~ s/ - (^CREATE\sTRIGGER\s.*?) - \sEXECUTE\sPROCEDURE - /$1 EXECUTE FUNCTION/mgx; - open(my $dh, '>', "$dump.fixed") || die "opening $dump.fixed"; - print $dh $contents; - close($dh); - } + open(my $odh, '>', "$olddumpfile.fixed") + || die "opening $olddumpfile.fixed: $!"; + print $odh $dump; + close($odh); = - system( qq{diff -I "^\$" -I "SET default_table_access_method =3D heap;" = } - . qq{ -I "^SET default_toast_compression =3D 'pglz';\$" -I "^-- " } - . qq{-u "$upgrade_loc/origin-$oversion.sql.fixed" } - . qq{"$upgrade_loc/converted-$oversion-to-$this_branch.sql.fixed" } + my $newdumpfile =3D "$upgrade_loc/converted-$oversion-to-$this_branch.sq= l"; + $dump =3D file_contents($newdumpfile); + + $dump =3D adjust_new_dumpfile($oversion, $dump); + + open(my $ndh, '>', "$newdumpfile.fixed") + || die "opening $newdumpfile.fixed: $!"; + print $ndh $dump; + close($ndh); + + system( qq{diff -u "$olddumpfile.fixed" "$newdumpfile.fixed" } . qq{> "$upgrade_loc/dumpdiff-$oversion" 2>&1}); = # diff exits with status 1 if files differ @@ -829,22 +643,7 @@ sub test_upgrade ## no critic (Subrou } close($diffile); = - # If the versions match we require that there be no diff lines. - # In the past we have seen a handful of diffs from reordering of - # large object output, but that appears to have disppeared. - # If the versions don't match we heuristically allow more lines of diffs - # based on observed differences. For versions from 9.6 on, that's - # not very many lines, though. - - if ( - ($oversion eq $this_branch && $difflines =3D=3D 0) - || ( $oversion ne $this_branch - && $oversion ge 'REL9_6_STABLE' - && $difflines < 90) - || ( $oversion ne $this_branch - && $oversion lt 'REL9_6_STABLE' - && $difflines < 700) - ) + if ($difflines =3D=3D 0) { return 1; } ------- =_aaaaaaaaaa0--