Skip to content

Commit fae5f08

Browse files
michaelpqmichaelpq
committed
Move into separate file all the SQL queries used in pg_upgrade tests
The existing pg_upgrade/test.sh and the buildfarm code have been holding the same set of SQL queries when doing cross-version upgrade tests to adapt the objects created by the regression tests before the upgrade (mostly, incompatible or non-existing objects need to be dropped from the origin, perhaps re-created). This moves all those SQL queries into a new, separate, file with a set of \if clauses to handle the version checks depending on the old version of the cluster to-be-upgraded. The long-term plan is to make the buildfarm code re-use this new SQL file, so as committers are able to fix any compatibility issues in the tests of pg_upgrade with a refresh of the core code, without having to poke at the buildfarm client. Note that this is only able to handle the main regression test suite, and that nothing is done yet for contrib modules yet (these have more issues like their database names). A backpatch down to 10 is done, adapting the version checks as this script needs to be only backward-compatible, so as it becomes possible to clean up a maximum amount of code within the buildfarm client. Author: Justin Pryzby, Michael Paquier Discussion: https://postgr.es/m/20201206180248.GI24052@telsasoft.com Backpatch-through: 10
1 parent 7413caa commit fae5f08

File tree

2 files changed

+85
-36
lines changed

2 files changed

+85
-36
lines changed

‎src/bin/pg_upgrade/test.sh

Lines changed: 5 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -176,42 +176,11 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
176176
# Before dumping, tweak the database of the old instance depending
177177
# on its version.
178178
if [ "$newsrc" != "$oldsrc" ]; then
179-
fix_sql=""
180-
# Get rid of objects not feasible in later versions
181-
case $oldpgversion in
182-
804??)
183-
fix_sql="DROP FUNCTION public.myfunc(integer);"
184-
;;
185-
esac
186-
187-
# Last appeared in v9.6
188-
if [ $oldpgversion -lt 100000 ]; then
189-
fix_sql="$fix_sql
190-
DROP FUNCTION IF EXISTS
191-
public.oldstyle_length(integer, text);"
192-
fi
193-
psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
194-
195-
# WITH OIDS is not supported anymore in v12, so remove support
196-
# for any relations marked as such.
197-
if [ $oldpgversion -lt 120000 ]; then
198-
fix_sql="DO \$stmt\$
199-
DECLARE
200-
rec text;
201-
BEGIN
202-
FOR rec in
203-
SELECT oid::regclass::text
204-
FROM pg_class
205-
WHERE relname !~ '^pg_'
206-
AND relhasoids
207-
AND relkind in ('r','m')
208-
ORDER BY 1
209-
LOOP
210-
execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';
211-
END LOOP;
212-
END; \$stmt\$;"
213-
psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
214-
fi
179+
# This SQL script has its own idea of the cleanup that needs to be
180+
# done on the cluster to-be-upgraded, and includes version checks.
181+
# Note that this uses the script stored on the new branch.
182+
psql -X -d regression -f "$newsrc/src/bin/pg_upgrade/upgrade_adapt.sql" \
183+
|| psql_fix_sql_status=$?
215184

216185
# Handling of --extra-float-digits gets messy after v12.
217186
# Note that this changes the dumps from the old and new

‎src/bin/pg_upgrade/upgrade_adapt.sql

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,80 @@
1+
--
2+
-- SQL queries for upgrade tests across different major versions.
3+
--
4+
-- This file includes a set of SQL queries to make a cluster to-be-upgraded
5+
-- compatible with the version this file is based on. Note that this
6+
-- requires psql, as per-version queries are controlled with a set of \if
7+
-- clauses.
8+
9+
-- This script is backward-compatible, so it is able to work with any version
10+
-- newer than 9.2 we are upgrading from, up to the branch this script is stored
11+
-- on (even if this would not run if running pg_upgrade with the same version
12+
-- for the origin and the target).
13+
14+
-- \if accepts a simple boolean value, so all the version checks are
15+
-- saved based on this assumption.
16+
SELECT
17+
ver <= 902 AS oldpgversion_le92,
18+
ver <= 904 AS oldpgversion_le94,
19+
ver <= 906 AS oldpgversion_le96,
20+
ver <= 1000 AS oldpgversion_le10,
21+
ver <= 1100 AS oldpgversion_le11
22+
FROM (SELECT current_setting('server_version_num')::int / 100 AS ver) AS v;
23+
\gset
24+
25+
-- Objects last appearing in 9.2.
26+
\if :oldpgversion_le92
27+
-- Note that those tables are removed from the regression tests in 9.3
28+
-- and newer versions.
29+
DROP TABLE abstime_tbl;
30+
DROP TABLE reltime_tbl;
31+
DROP TABLE tinterval_tbl;
32+
\endif
33+
34+
-- Objects last appearing in 9.4.
35+
\if :oldpgversion_le94
36+
-- This aggregate has been fixed in 9.5 and later versions, so drop
37+
-- and re-create it.
38+
DROP AGGREGATE array_cat_accum(anyarray);
39+
CREATE AGGREGATE array_larger_accum (anyarray) (
40+
sfunc = array_larger,
41+
stype = anyarray,
42+
initcond = $${}$$);
43+
-- This operator has been fixed in 9.5 and later versions, so drop and
44+
-- re-create it.
45+
DROP OPERATOR @#@ (NONE, bigint);
46+
CREATE OPERATOR @#@ (PROCEDURE = factorial,
47+
RIGHTARG = bigint);
48+
\endif
49+
50+
-- Objects last appearing in 9.6.
51+
\if :oldpgversion_le96
52+
DROP FUNCTION public.oldstyle_length(integer, text);
53+
\endif
54+
55+
-- Objects last appearing in 10.
56+
\if :oldpgversion_le10
57+
DROP FUNCTION IF EXISTS boxarea(box);
58+
DROP FUNCTION IF EXISTS funny_dup17();
59+
\endif
60+
61+
-- Objects last appearing in 11.
62+
\if :oldpgversion_le11
63+
-- WITH OIDS is supported until v11, so remove its support for any
64+
-- relations marked as such.
65+
DO $stmt$
66+
DECLARE
67+
rec text;
68+
BEGIN
69+
FOR rec in
70+
SELECT oid::regclass::text
71+
FROM pg_class
72+
WHERE relname !~ '^pg_'
73+
AND relhasoids
74+
AND relkind in ('r','m')
75+
ORDER BY 1
76+
LOOP
77+
execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';
78+
END LOOP;
79+
END; $stmt$;
80+
\endif

0 commit comments

Comments
 (0)