From 9e710615e610f055de9ae4675704fd7429dd8155 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 6 Mar 2021 18:35:26 -0600 Subject: [PATCH v5 4/4] Move pg_upgrade kludges to sql script --- src/bin/pg_upgrade/test-upgrade.sql | 70 +++++++++++++++++++++ src/bin/pg_upgrade/test.sh | 97 +---------------------------- 2 files changed, 71 insertions(+), 96 deletions(-) create mode 100644 src/bin/pg_upgrade/test-upgrade.sql diff --git a/src/bin/pg_upgrade/test-upgrade.sql b/src/bin/pg_upgrade/test-upgrade.sql new file mode 100644 index 0000000000..8c7cceb211 --- /dev/null +++ b/src/bin/pg_upgrade/test-upgrade.sql @@ -0,0 +1,70 @@ +-- This file has a bunch of kludges needed for testing upgrades across major versions + +SELECT + ver >= 804 AND ver <= 1100 AS oldpgversion_84_11, + ver >= 905 AND ver <= 1300 AS oldpgversion_95_13, + ver >= 906 AND ver <= 1300 AS oldpgversion_96_13, + ver >= 906 AND ver <= 1000 AS oldpgversion_96_10, + ver >= 1000 AS oldpgversion_ge10, + ver <= 804 AS oldpgversion_le84, + ver <= 1300 AS oldpgversion_le13 + FROM (SELECT current_setting('server_version_num')::int/100 AS ver) AS v; +\gset + +\if :oldpgversion_le84 +DROP FUNCTION public.myfunc(integer); +\endif + +-- last in 9.6 -- commit 5ded4bd21 +DROP FUNCTION IF EXISTS public.oldstyle_length(integer, text); +DROP FUNCTION IF EXISTS public.putenv(text); + +\if :oldpgversion_le13 +-- last in v13 commit 76f412ab3 +-- public.!=- This one is only needed for v11+ ?? +-- Note, until v10, operators could only be dropped one at a time +DROP OPERATOR IF EXISTS public.#@# (pg_catalog.int8, NONE); +DROP OPERATOR IF EXISTS public.#%# (pg_catalog.int8, NONE); +DROP OPERATOR IF EXISTS public.!=- (pg_catalog.int8, NONE); +DROP OPERATOR IF EXISTS public.#@%# (pg_catalog.int8, NONE); +\endif + +\if :oldpgversion_ge10 +-- commit 068503c76511cdb0080bab689662a20e86b9c845 +DROP TRANSFORM FOR integer LANGUAGE sql CASCADE; +\endif + +\if :oldpgversion_96_10 +-- commit db3af9feb19f39827e916145f88fa5eca3130cb2 +DROP FUNCTION boxarea(box); +DROP FUNCTION funny_dup17(); + +-- commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f +DROP TABLE abstime_tbl; +DROP TABLE reltime_tbl; +DROP TABLE tinterval_tbl; +\endif + +\if :oldpgversion_96_13 +-- Various things removed for v14 +DROP AGGREGATE first_el_agg_any(anyelement); +\endif + +\if :oldpgversion_95_13 +-- commit 9e38c2bb5 and 97f73a978 +-- DROP AGGREGATE array_larger_accum(anyarray); +DROP AGGREGATE array_cat_accum(anyarray); + +-- commit 76f412ab3 +-- DROP OPERATOR @#@(bigint,NONE); +DROP OPERATOR @#@(NONE,bigint); +\endif + +\if :oldpgversion_84_11 +-- commit 578b22971: OIDS removed in v12 +ALTER TABLE public.tenk1 SET WITHOUT OIDS; +ALTER TABLE public.tenk1 SET WITHOUT OIDS; +-- fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited +ALTER TABLE public.emp SET WITHOUT OIDS; +ALTER TABLE public.tt7 SET WITHOUT OIDS; +\endif diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh index 2bdd8c19de..61bcca3673 100644 --- a/src/bin/pg_upgrade/test.sh +++ b/src/bin/pg_upgrade/test.sh @@ -177,104 +177,9 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then # before dumping, get rid of objects not feasible in later versions if [ "$newsrc" != "$oldsrc" ]; then - fix_sql="" - case $oldpgversion in - 804??) - fix_sql="DROP FUNCTION public.myfunc(integer);" - ;; - esac - fix_sql="$fix_sql - DROP FUNCTION IF EXISTS - public.oldstyle_length(integer, text);" # last in 9.6 -- commit 5ded4bd21 - fix_sql="$fix_sql - DROP FUNCTION IF EXISTS - public.putenv(text);" # last in v13 - # last in v13 commit 76f412ab3 - # public.!=- This one is only needed for v11+ ?? - # Note, until v10, operators could only be dropped one at a time - fix_sql="$fix_sql - DROP OPERATOR IF EXISTS - public.#@# (pg_catalog.int8, NONE);" - fix_sql="$fix_sql - DROP OPERATOR IF EXISTS - public.#%# (pg_catalog.int8, NONE);" - fix_sql="$fix_sql - DROP OPERATOR IF EXISTS - public.!=- (pg_catalog.int8, NONE);" - fix_sql="$fix_sql - DROP OPERATOR IF EXISTS - public.#@%# (pg_catalog.int8, NONE);" - - # commit 068503c76511cdb0080bab689662a20e86b9c845 - case $oldpgversion in - 10????) - fix_sql="$fix_sql - DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;" - ;; - esac - - # commit db3af9feb19f39827e916145f88fa5eca3130cb2 - case $oldpgversion in - 10????) - fix_sql="$fix_sql - DROP FUNCTION boxarea(box);" - fix_sql="$fix_sql - DROP FUNCTION funny_dup17();" - ;; - esac - - # commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f - case $oldpgversion in - 10????) - fix_sql="$fix_sql - DROP TABLE abstime_tbl;" - fix_sql="$fix_sql - DROP TABLE reltime_tbl;" - fix_sql="$fix_sql - DROP TABLE tinterval_tbl;" - ;; - esac - - # Various things removed for v14 - case $oldpgversion in - 906??|10????|11????|12????|13????) - fix_sql="$fix_sql - DROP AGGREGATE first_el_agg_any(anyelement);" - ;; - esac - case $oldpgversion in - 90[56]??|10????|11????|12????|13????) - # commit 9e38c2bb5 and 97f73a978 - # fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);" - fix_sql="$fix_sql - DROP AGGREGATE array_cat_accum(anyarray);" - - # commit 76f412ab3 - #fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);" - fix_sql="$fix_sql - DROP OPERATOR @#@(NONE,bigint);" - ;; - esac - - # commit 578b22971: OIDS removed in v12 - case $oldpgversion in - 804??|9????|10????|11????) - fix_sql="$fix_sql - ALTER TABLE public.tenk1 SET WITHOUT OIDS;" - fix_sql="$fix_sql - ALTER TABLE public.tenk1 SET WITHOUT OIDS;" - #fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited - fix_sql="$fix_sql - ALTER TABLE public.emp SET WITHOUT OIDS;" - fix_sql="$fix_sql - ALTER TABLE public.tt7 SET WITHOUT OIDS;" - ;; - esac - - psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$? + psql -X -d regression -f "test-upgrade.sql" || psql_fix_sql_status=$? fi - echo "fix_sql: $oldpgversion: $fix_sql" >&2 pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$? if [ "$newsrc" != "$oldsrc" ]; then -- 2.17.0