Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4bcf352

Browse files
committed
Create common infrastructure for cross-version upgrade testing.
To test pg_upgrade across major PG versions, we have to be able tomodify or drop any old objects with no-longer-supported properties,and we have to be able to deal with cosmetic changes in pg_dump output.Up to now, the buildfarm and pg_upgrade's own test infrastructure hadseparate implementations of the former, and we had nothing but veryad-hoc rules for the latter (including an arbitrary threshold on howmany lines of unchecked diff were okay!). This patch creates a Perlmodule that can be shared by both those use-cases, and adds logicthat deals with pg_dump output diffs in a much more tightly definedfashion.This largely supersedes previous efforts in commits0df9641,9814ff5, and62be9e4, which developed a SQL-script-based solutionfor the task of dropping old objects. There was nothing fundamentallywrong with that work in itself, but it had no basis for solving theoutput-formatting problem. The most plausible way to deal withformatting is to build a Perl module that can perform editing on thedump files; and once we commit to that, it makes more sense for thesame module to also embed the knowledge of what has to be done fordropping old objects.Back-patch versions of the helper module as far as 9.2, tosupport buildfarm animals that still test that far back.It's also necessary to back-patch PostgreSQL/Version.pm,because the new code depends on that. I fixed up pg_upgrade's002_pg_upgrade.pl in v15, but did not look into back-patchingit further than that.Tom Lane and Andrew DunstanDiscussion:https://postgr.es/m/891521.1673657296@sss.pgh.pa.us
1 parent50bee42 commit4bcf352

File tree

2 files changed

+460
-0
lines changed

2 files changed

+460
-0
lines changed
Lines changed: 293 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,293 @@
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+
return$result;
115+
}
116+
117+
# Internal subroutine to add statement(s) to the list for the given db.
118+
sub_add_st
119+
{
120+
my ($result,$db,@st) =@_;
121+
122+
$result->{$db} ||= [];
123+
push(@{$result->{$db} },@st);
124+
}
125+
126+
=pod
127+
128+
=itemadjust_old_dumpfile($old_version, $dump)
129+
130+
Edit a dump output file, taken from the adjusted old-version installation
131+
by current-versionC<pg_dumpall -s>, so that it will match the results of
132+
C<pg_dumpall -s> on the pg_upgrade'd installation.
133+
134+
Typically this involves coping with cosmetic differences in the output
135+
of backend subroutines used by pg_dump.
136+
137+
Arguments:
138+
139+
=over
140+
141+
=itemC<old_version>: Branch we are upgrading from, represented as a
142+
PostgreSQL::Version object.
143+
144+
=itemC<dump>: Contents of dump file
145+
146+
=back
147+
148+
Returns the modified dump text.
149+
150+
=cut
151+
152+
subadjust_old_dumpfile
153+
{
154+
my ($old_version,$dump) =@_;
155+
156+
# use Unix newlines
157+
$dump =~s/\r\n/\n/g;
158+
159+
# Version comments will certainly not match.
160+
$dump =~s/^-- Dumped from database version.*\n//mg;
161+
162+
if ($old_versionlt'9.3')
163+
{
164+
# CREATE VIEW/RULE statements were not pretty-printed before 9.3.
165+
# To cope, reduce all whitespace sequences within them to one space.
166+
# This must be done on both old and new dumps.
167+
$dump = _mash_view_whitespace($dump);
168+
169+
# _mash_view_whitespace doesn't handle multi-command rules;
170+
# rather than trying to fix that, just hack the exceptions manually.
171+
172+
my$prefix =
173+
"CREATE RULE rtest_sys_del AS ON DELETE TO public.rtest_system DO (DELETE FROM public.rtest_interface WHERE (rtest_interface.sysname = old.sysname);";
174+
my$line2 =" DELETE FROM public.rtest_admin";
175+
my$line3 =" WHERE (rtest_admin.sysname = old.sysname);";
176+
$dump =~
177+
s/(?<=\Q$prefix\E)\Q$line2$line3\E\);/\n$line2\n$line3\n);/mg;
178+
179+
$prefix =
180+
"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);";
181+
$line2 =" UPDATE public.rtest_admin SET sysname = new.sysname";
182+
$line3 =" WHERE (rtest_admin.sysname = old.sysname);";
183+
$dump =~
184+
s/(?<=\Q$prefix\E)\Q$line2$line3\E\);/\n$line2\n$line3\n);/mg;
185+
186+
# and there's one place where pre-9.3 uses a different table alias
187+
$dump =~s {^(CREATE\sRULE\srule_and_refint_t3_ins\sAS\s
188+
ON\sINSERT\sTO\spublic\.rule_and_refint_t3\s
189+
WHERE\s\(EXISTS\s\(SELECT\s1\sFROM\spublic\.rule_and_refint_t3)\s
190+
(WHERE\s\(\(\(rule_and_refint_t3)
191+
(\.id3a\s=\snew\.id3a\)\sAND\s\(rule_and_refint_t3)
192+
(\.id3b\s=\snew\.id3b\)\)\sAND\s\(rule_and_refint_t3)}
193+
{$1 rule_and_refint_t3_1 $2_1$3_1$4_1}mx;
194+
195+
# Also fix old use of NATURAL JOIN syntax
196+
$dump =~s {NATURAL JOIN public\.credit_card r}
197+
{JOIN public.credit_card r USING (cid)}mg;
198+
$dump =~s {NATURAL JOIN public\.credit_usage r}
199+
{JOIN public.credit_usage r USING (cid)}mg;
200+
}
201+
202+
# Suppress blank lines, as some places in pg_dump emit more or fewer.
203+
$dump =~s/\n\n+/\n/g;
204+
205+
return$dump;
206+
}
207+
208+
# Internal subroutine to mangle whitespace within view/rule commands.
209+
# Any consecutive sequence of whitespace is reduced to one space.
210+
sub_mash_view_whitespace
211+
{
212+
my ($dump) =@_;
213+
214+
foreachmy$leader ('CREATE VIEW','CREATE RULE')
215+
{
216+
my@splitchunks =split$leader,$dump;
217+
218+
$dump =shift(@splitchunks);
219+
foreachmy$chunk (@splitchunks)
220+
{
221+
my@thischunks =split /;/,$chunk, 2;
222+
my$stmt =shift(@thischunks);
223+
224+
# now $stmt is just the body of the CREATE VIEW/RULE
225+
$stmt =~s/\s+//sg;
226+
# we also need to smash these forms for sub-selects and rules
227+
$stmt =~s/\( SELECT/(SELECT/g;
228+
$stmt =~s/\( INSERT/(INSERT/g;
229+
$stmt =~s/\( UPDATE/(UPDATE/g;
230+
$stmt =~s/\( DELETE/(DELETE/g;
231+
232+
$dump .=$leader .$stmt .';' .$thischunks[0];
233+
}
234+
}
235+
return$dump;
236+
}
237+
238+
=pod
239+
240+
=itemadjust_new_dumpfile($old_version, $dump)
241+
242+
Edit a dump output file, taken from the pg_upgrade'd installation
243+
by current-versionC<pg_dumpall -s>, so that it will match the old
244+
dump output file as adjusted byC<adjust_old_dumpfile>.
245+
246+
Typically this involves deleting data not present in the old installation.
247+
248+
Arguments:
249+
250+
=over
251+
252+
=itemC<old_version>: Branch we are upgrading from, represented as a
253+
PostgreSQL::Version object.
254+
255+
=itemC<dump>: Contents of dump file
256+
257+
=back
258+
259+
Returns the modified dump text.
260+
261+
=cut
262+
263+
subadjust_new_dumpfile
264+
{
265+
my ($old_version,$dump) =@_;
266+
267+
# use Unix newlines
268+
$dump =~s/\r\n/\n/g;
269+
270+
# Version comments will certainly not match.
271+
$dump =~s/^-- Dumped from database version.*\n//mg;
272+
273+
if ($old_versionlt'9.3')
274+
{
275+
# CREATE VIEW/RULE statements were not pretty-printed before 9.3.
276+
# To cope, reduce all whitespace sequences within them to one space.
277+
# This must be done on both old and new dumps.
278+
$dump = _mash_view_whitespace($dump);
279+
}
280+
281+
# Suppress blank lines, as some places in pg_dump emit more or fewer.
282+
$dump =~s/\n\n+/\n/g;
283+
284+
return$dump;
285+
}
286+
287+
=pod
288+
289+
=back
290+
291+
=cut
292+
293+
1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp