|
| 1 | + |
| 2 | +# Copyright (c) 2023, PostgreSQL Global Development Group |
| 3 | + |
| 4 | +=pod |
| 5 | +
|
| 6 | +=head1NAME |
| 7 | +
|
| 8 | +PostgreSQL::Test::AdjustUpgrade - helper module for cross-version upgrade tests |
| 9 | +
|
| 10 | +=head1SYNOPSIS |
| 11 | +
|
| 12 | + use PostgreSQL::Test::AdjustUpgrade; |
| 13 | +
|
| 14 | + # Build commands to adjust contents of old-version database before dumping |
| 15 | + $statements = adjust_database_contents($old_version, %dbnames); |
| 16 | +
|
| 17 | + # Adjust contents of old pg_dumpall output file to match newer version |
| 18 | + $dump = adjust_old_dumpfile($old_version, $dump); |
| 19 | +
|
| 20 | + # Adjust contents of new pg_dumpall output file to match older version |
| 21 | + $dump = adjust_new_dumpfile($old_version, $dump); |
| 22 | +
|
| 23 | +=head1DESCRIPTION |
| 24 | +
|
| 25 | +C<PostgreSQL::Test::AdjustUpgrade> encapsulates various hacks needed to |
| 26 | +compare the results of cross-version upgrade tests. |
| 27 | +
|
| 28 | +=cut |
| 29 | + |
| 30 | +packagePostgreSQL::Test::AdjustUpgrade; |
| 31 | + |
| 32 | +use strict; |
| 33 | +use warnings; |
| 34 | + |
| 35 | +use Exporter'import'; |
| 36 | +use PostgreSQL::Version; |
| 37 | + |
| 38 | +our@EXPORT =qw( |
| 39 | + adjust_database_contents |
| 40 | + adjust_old_dumpfile |
| 41 | + adjust_new_dumpfile |
| 42 | +); |
| 43 | + |
| 44 | +=pod |
| 45 | +
|
| 46 | +=head1ROUTINES |
| 47 | +
|
| 48 | +=over |
| 49 | +
|
| 50 | +=item$statements = adjust_database_contents($old_version, %dbnames) |
| 51 | +
|
| 52 | +Generate SQL commands to perform any changes to an old-version installation |
| 53 | +that are needed before we can pg_upgrade it into the current PostgreSQL |
| 54 | +version. |
| 55 | +
|
| 56 | +Typically this involves dropping or adjusting no-longer-supported objects. |
| 57 | +
|
| 58 | +Arguments: |
| 59 | +
|
| 60 | +=over |
| 61 | +
|
| 62 | +=itemC<old_version>: Branch we are upgrading from, represented as a |
| 63 | +PostgreSQL::Version object. |
| 64 | +
|
| 65 | +=itemC<dbnames>: Hash of database names present in the old installation. |
| 66 | +
|
| 67 | +=back |
| 68 | +
|
| 69 | +Returns a reference to a hash, wherein the keys are database names and the |
| 70 | +values are arrayrefs to lists of statements to be run in those databases. |
| 71 | +
|
| 72 | +=cut |
| 73 | + |
| 74 | +subadjust_database_contents |
| 75 | +{ |
| 76 | +my ($old_version,%dbnames) =@_; |
| 77 | +my$result = {}; |
| 78 | + |
| 79 | +# remove dbs of modules known to cause pg_upgrade to fail |
| 80 | +# anything not builtin and incompatible should clean up its own db |
| 81 | +foreachmy$bad_module ('test_ddl_deparse','tsearch2') |
| 82 | +{ |
| 83 | +if ($dbnames{"contrib_regression_$bad_module"}) |
| 84 | +{ |
| 85 | +_add_st($result,'postgres', |
| 86 | +"drop database contrib_regression_$bad_module"); |
| 87 | +delete($dbnames{"contrib_regression_$bad_module"}); |
| 88 | +} |
| 89 | +} |
| 90 | + |
| 91 | +# avoid version number issues with test_ext7 |
| 92 | +if ($dbnames{contrib_regression_test_extensions}) |
| 93 | +{ |
| 94 | +_add_st( |
| 95 | +$result, |
| 96 | +'contrib_regression_test_extensions', |
| 97 | +'drop extension if exists test_ext7'); |
| 98 | +} |
| 99 | + |
| 100 | +# get rid of dblink's dependencies on regress.so |
| 101 | +my$regrdb = |
| 102 | +$old_versionle'9.4' |
| 103 | + ?'contrib_regression' |
| 104 | + :'contrib_regression_dblink'; |
| 105 | + |
| 106 | +if ($dbnames{$regrdb}) |
| 107 | +{ |
| 108 | +_add_st( |
| 109 | +$result,$regrdb, |
| 110 | +'drop function if exists public.putenv(text)', |
| 111 | +'drop function if exists public.wait_pid(integer)'); |
| 112 | +} |
| 113 | + |
| 114 | +if ($old_versionlt'9.5') |
| 115 | +{ |
| 116 | +# cope with changes of underlying functions |
| 117 | +_add_st( |
| 118 | +$result, |
| 119 | +'regression', |
| 120 | +'drop operator @#@ (NONE, bigint)', |
| 121 | +'CREATE OPERATOR @#@ (' |
| 122 | + .'PROCEDURE = factorial, RIGHTARG = bigint )', |
| 123 | +'drop aggregate public.array_cat_accum(anyarray)', |
| 124 | +'CREATE AGGREGATE array_larger_accum (anyarray)' .' (' |
| 125 | + .' sfunc = array_larger,' |
| 126 | + .' stype = anyarray,' |
| 127 | + .' initcond = $${}$$' .' )'); |
| 128 | + |
| 129 | +# "=>" is no longer valid as an operator name |
| 130 | +_add_st($result,'regression', |
| 131 | +'drop operator if exists public.=> (bigint, NONE)'); |
| 132 | +} |
| 133 | + |
| 134 | +return$result; |
| 135 | +} |
| 136 | + |
| 137 | +# Internal subroutine to add statement(s) to the list for the given db. |
| 138 | +sub_add_st |
| 139 | +{ |
| 140 | +my ($result,$db,@st) =@_; |
| 141 | + |
| 142 | +$result->{$db} ||= []; |
| 143 | +push(@{$result->{$db} },@st); |
| 144 | +} |
| 145 | + |
| 146 | +=pod |
| 147 | +
|
| 148 | +=itemadjust_old_dumpfile($old_version, $dump) |
| 149 | +
|
| 150 | +Edit a dump output file, taken from the adjusted old-version installation |
| 151 | +by current-versionC<pg_dumpall -s>, so that it will match the results of |
| 152 | +C<pg_dumpall -s> on the pg_upgrade'd installation. |
| 153 | +
|
| 154 | +Typically this involves coping with cosmetic differences in the output |
| 155 | +of backend subroutines used by pg_dump. |
| 156 | +
|
| 157 | +Arguments: |
| 158 | +
|
| 159 | +=over |
| 160 | +
|
| 161 | +=itemC<old_version>: Branch we are upgrading from, represented as a |
| 162 | +PostgreSQL::Version object. |
| 163 | +
|
| 164 | +=itemC<dump>: Contents of dump file |
| 165 | +
|
| 166 | +=back |
| 167 | +
|
| 168 | +Returns the modified dump text. |
| 169 | +
|
| 170 | +=cut |
| 171 | + |
| 172 | +subadjust_old_dumpfile |
| 173 | +{ |
| 174 | +my ($old_version,$dump) =@_; |
| 175 | + |
| 176 | +# use Unix newlines |
| 177 | +$dump =~s/\r\n/\n/g; |
| 178 | + |
| 179 | +# Version comments will certainly not match. |
| 180 | +$dump =~s/^-- Dumped from database version.*\n//mg; |
| 181 | + |
| 182 | +if ($old_versionlt'9.5') |
| 183 | +{ |
| 184 | +# adjust some places where we don't print so many parens anymore |
| 185 | + |
| 186 | +my$prefix ="CONSTRAINT (?:sequence|copy)_con CHECK [(][(]"; |
| 187 | +my$orig ="((x > 3) AND (y <> 'check failed'::text))"; |
| 188 | +my$repl ="(x > 3) AND (y <> 'check failed'::text)"; |
| 189 | +$dump =~s/($prefix)\Q$orig\E/$1$repl/mg; |
| 190 | + |
| 191 | +$prefix ="CONSTRAINT insert_con CHECK [(][(]"; |
| 192 | +$orig ="((x >= 3) AND (y <> 'check failed'::text))"; |
| 193 | +$repl ="(x >= 3) AND (y <> 'check failed'::text)"; |
| 194 | +$dump =~s/($prefix)\Q$orig\E/$1$repl/mg; |
| 195 | + |
| 196 | +$orig ="DEFAULT ((-1) * currval('public.insert_seq'::regclass))"; |
| 197 | +$repl = |
| 198 | +"DEFAULT ('-1'::integer * currval('public.insert_seq'::regclass))"; |
| 199 | +$dump =~s/\Q$orig\E/$repl/mg; |
| 200 | + |
| 201 | +my$expr = |
| 202 | +"(rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)"; |
| 203 | +$dump =~s/WHERE\(\(\Q$expr\E\)/WHERE ($expr/g; |
| 204 | + |
| 205 | +$expr = |
| 206 | +"(rule_and_refint_t3.id3a = new.id3a) AND (rule_and_refint_t3.id3b = new.id3b)"; |
| 207 | +$dump =~s/WHERE\(\(\Q$expr\E\)/WHERE ($expr/g; |
| 208 | + |
| 209 | +$expr = |
| 210 | +"(rule_and_refint_t3_1.id3a = new.id3a) AND (rule_and_refint_t3_1.id3b = new.id3b)"; |
| 211 | +$dump =~s/WHERE\(\(\Q$expr\E\)/WHERE ($expr/g; |
| 212 | +} |
| 213 | + |
| 214 | +if ($old_versionlt'9.3') |
| 215 | +{ |
| 216 | +# CREATE VIEW/RULE statements were not pretty-printed before 9.3. |
| 217 | +# To cope, reduce all whitespace sequences within them to one space. |
| 218 | +# This must be done on both old and new dumps. |
| 219 | +$dump = _mash_view_whitespace($dump); |
| 220 | + |
| 221 | +# _mash_view_whitespace doesn't handle multi-command rules; |
| 222 | +# rather than trying to fix that, just hack the exceptions manually. |
| 223 | + |
| 224 | +my$prefix = |
| 225 | +"CREATE RULE rtest_sys_del AS ON DELETE TO public.rtest_system DO (DELETE FROM public.rtest_interface WHERE (rtest_interface.sysname = old.sysname);"; |
| 226 | +my$line2 =" DELETE FROM public.rtest_admin"; |
| 227 | +my$line3 =" WHERE (rtest_admin.sysname = old.sysname);"; |
| 228 | +$dump =~ |
| 229 | +s/(?<=\Q$prefix\E)\Q$line2$line3\E\);/\n$line2\n$line3\n);/mg; |
| 230 | + |
| 231 | +$prefix = |
| 232 | +"CREATE RULE rtest_sys_upd AS ON UPDATE TO public.rtest_system DO (UPDATE public.rtest_interface SET sysname = new.sysname WHERE (rtest_interface.sysname = old.sysname);"; |
| 233 | +$line2 =" UPDATE public.rtest_admin SET sysname = new.sysname"; |
| 234 | +$line3 =" WHERE (rtest_admin.sysname = old.sysname);"; |
| 235 | +$dump =~ |
| 236 | +s/(?<=\Q$prefix\E)\Q$line2$line3\E\);/\n$line2\n$line3\n);/mg; |
| 237 | + |
| 238 | +# and there's one place where pre-9.3 uses a different table alias |
| 239 | +$dump =~s {^(CREATE\sRULE\srule_and_refint_t3_ins\sAS\s |
| 240 | + ON\sINSERT\sTO\spublic\.rule_and_refint_t3\s |
| 241 | + WHERE\s\(EXISTS\s\(SELECT\s1\sFROM\spublic\.rule_and_refint_t3)\s |
| 242 | + (WHERE\s\(\(rule_and_refint_t3) |
| 243 | + (\.id3a\s=\snew\.id3a\)\sAND\s\(rule_and_refint_t3) |
| 244 | + (\.id3b\s=\snew\.id3b\)\sAND\s\(rule_and_refint_t3)} |
| 245 | +{$1 rule_and_refint_t3_1 $2_1$3_1$4_1}mx; |
| 246 | + |
| 247 | +# Also fix old use of NATURAL JOIN syntax |
| 248 | +$dump =~s {NATURAL JOIN public\.credit_card r} |
| 249 | +{JOIN public.credit_card r USING (cid)}mg; |
| 250 | +$dump =~s {NATURAL JOIN public\.credit_usage r} |
| 251 | +{JOIN public.credit_usage r USING (cid)}mg; |
| 252 | +} |
| 253 | + |
| 254 | +# Suppress blank lines, as some places in pg_dump emit more or fewer. |
| 255 | +$dump =~s/\n\n+/\n/g; |
| 256 | + |
| 257 | +return$dump; |
| 258 | +} |
| 259 | + |
| 260 | +# Internal subroutine to mangle whitespace within view/rule commands. |
| 261 | +# Any consecutive sequence of whitespace is reduced to one space. |
| 262 | +sub_mash_view_whitespace |
| 263 | +{ |
| 264 | +my ($dump) =@_; |
| 265 | + |
| 266 | +foreachmy$leader ('CREATE VIEW','CREATE RULE') |
| 267 | +{ |
| 268 | +my@splitchunks =split$leader,$dump; |
| 269 | + |
| 270 | +$dump =shift(@splitchunks); |
| 271 | +foreachmy$chunk (@splitchunks) |
| 272 | +{ |
| 273 | +my@thischunks =split /;/,$chunk, 2; |
| 274 | +my$stmt =shift(@thischunks); |
| 275 | + |
| 276 | +# now $stmt is just the body of the CREATE VIEW/RULE |
| 277 | +$stmt =~s/\s+//sg; |
| 278 | +# we also need to smash these forms for sub-selects and rules |
| 279 | +$stmt =~s/\( SELECT/(SELECT/g; |
| 280 | +$stmt =~s/\( INSERT/(INSERT/g; |
| 281 | +$stmt =~s/\( UPDATE/(UPDATE/g; |
| 282 | +$stmt =~s/\( DELETE/(DELETE/g; |
| 283 | + |
| 284 | +$dump .=$leader .$stmt .';' .$thischunks[0]; |
| 285 | +} |
| 286 | +} |
| 287 | +return$dump; |
| 288 | +} |
| 289 | + |
| 290 | +=pod |
| 291 | +
|
| 292 | +=itemadjust_new_dumpfile($old_version, $dump) |
| 293 | +
|
| 294 | +Edit a dump output file, taken from the pg_upgrade'd installation |
| 295 | +by current-versionC<pg_dumpall -s>, so that it will match the old |
| 296 | +dump output file as adjusted byC<adjust_old_dumpfile>. |
| 297 | +
|
| 298 | +Typically this involves deleting data not present in the old installation. |
| 299 | +
|
| 300 | +Arguments: |
| 301 | +
|
| 302 | +=over |
| 303 | +
|
| 304 | +=itemC<old_version>: Branch we are upgrading from, represented as a |
| 305 | +PostgreSQL::Version object. |
| 306 | +
|
| 307 | +=itemC<dump>: Contents of dump file |
| 308 | +
|
| 309 | +=back |
| 310 | +
|
| 311 | +Returns the modified dump text. |
| 312 | +
|
| 313 | +=cut |
| 314 | + |
| 315 | +subadjust_new_dumpfile |
| 316 | +{ |
| 317 | +my ($old_version,$dump) =@_; |
| 318 | + |
| 319 | +# use Unix newlines |
| 320 | +$dump =~s/\r\n/\n/g; |
| 321 | + |
| 322 | +# Version comments will certainly not match. |
| 323 | +$dump =~s/^-- Dumped from database version.*\n//mg; |
| 324 | + |
| 325 | +if ($old_versionlt'9.3') |
| 326 | +{ |
| 327 | +# CREATE VIEW/RULE statements were not pretty-printed before 9.3. |
| 328 | +# To cope, reduce all whitespace sequences within them to one space. |
| 329 | +# This must be done on both old and new dumps. |
| 330 | +$dump = _mash_view_whitespace($dump); |
| 331 | +} |
| 332 | + |
| 333 | +# Suppress blank lines, as some places in pg_dump emit more or fewer. |
| 334 | +$dump =~s/\n\n+/\n/g; |
| 335 | + |
| 336 | +return$dump; |
| 337 | +} |
| 338 | + |
| 339 | +=pod |
| 340 | +
|
| 341 | +=back |
| 342 | +
|
| 343 | +=cut |
| 344 | + |
| 345 | +1; |