From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: buschmann@nidsa.net, pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
Subject: Re: pg_upgrade test for binary compatibility of core data types
Date: Fri, 30 Apr 2021 13:33:48 -0500
Message-ID: <20210430183348.GQ27406@telsasoft.com> (raw)
In-Reply-To: <1575064.1615060903@sss.pgh.pa.us>
References: <20201216172222.GS30237@telsasoft.com>
<20201227190729.GB26311@telsasoft.com>
<be693563-def1-2f5a-89c1-f4d3cb2ae716@enterprisedb.com>
<20210112041352.GY1849@telsasoft.com>
<20210112171559.GO4320@momjian.us>
<20210112172753.GC1849@telsasoft.com>
<20210112175356.GP4320@momjian.us>
<66753439-83ae-7be5-6e41-9537a6041a39@dunslane.net>
<d9f14840-9f6e-183d-f43a-9f9b9e1b6d19@enterprisedb.com>
<1575064.1615060903@sss.pgh.pa.us>
On Sat, Mar 06, 2021 at 03:01:43PM -0500, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> > On 2021-01-12 22:44, Andrew Dunstan wrote:
> >> Cross version pg_upgrade is tested regularly in the buildfarm, but not
> >> using test.sh. Instead it uses the saved data repository from a previous
> >> run of the buildfarm client for the source branch, and tries to upgrade
> >> that to the target branch.
>
> > Does it maintain a set of fixups similar to what is in test.sh? Are
> > those two sets the same?
>
> Responding to Peter: the first answer is yes, the second is I didn't
> check, but certainly Justin's patch makes them closer.
Right - I had meant to send this.
https://github.com/PGBuildFarm/client-code/blob/master/PGBuild/Modules/TestUpgradeXversion.pm
$opsql = 'drop operator if exists public.=> (bigint, NONE)';
..
my $missing_funcs = q{drop function if exists public.boxarea(box);
drop function if exists public.funny_dup17();
..
my $prstmt = join(';',
'drop operator if exists #@# (bigint,NONE)',
'drop operator if exists #%# (bigint,NONE)',
'drop operator if exists !=- (bigint,NONE)',
..
$prstmt = join(';',
'drop operator @#@ (NONE, bigint)',
..
'drop aggregate if exists public.array_cat_accum(anyarray)',
> I spent some time poking through this set of patches. I agree that
> there's problem(s) here that we need to solve, but it feels like this
> isn't a great way to solve them. What I see in the patchset is:
For starters, is there a "release beta checklist" ?
Testing test.sh should be on it.
So should fuzz testing.
> v4-0001 mostly teaches test.sh about specific changes that have to be
> made to historic versions of the regression database to allow them
> to be reloaded into current servers. As already discussed, this is
> really duplicative of knowledge that's been embedded into the buildfarm
> client over time. It'd be better if we could refactor that so that
> the buildfarm shares a common database of these actions with test.sh.
> And said database ought to be in our git tree, so committers could
> fix problems without having to get Andrew involved every time.
> I think this could be represented as a psql script, at least in
> versions that have psql \if (but that came in in v10, so maybe
> we're there already).
I started this. I don't know if it's compatible with the buildfarm client, but
I think any issues maybe can be avoided by using "IF EXISTS".
> v4-0002 is a bunch of random changes that mostly seem to revert hacky
> adjustments previously made to improve test coverage. I don't really
> agree with any of these, nor see why they're necessary. If they
> are necessary then we need to restore the coverage somewhere else.
> Admittedly, the previous changes were a bit hacky, but deleting them
> (without even bothering to adjust the relevant comments) isn't the
> answer.
It was necessary to avoid --wal-segsize and -g to allow testing upgrades from
versions which don't support those options. I think test.sh should be portable
back to all supported versions.
When those options were added, it broke test.sh upgrading from old versions.
I changed this to a shell conditional for the "new" features:
| "$1" -N -A trust ${oldsrc:+--wal-segsize 1 -g}
Ideally it would check the version.
> v4-0003 is really the heart of the matter: it adds a table with some
> previously-not-covered datatypes plus a query that purports to make sure
> that we are covering all types of interest.
Actually the 'manytypes' table intends to include *all* core datatypes itself,
not just those that aren't included somewhere else. I think "included
somewhere else" depends on the order of the regression these, and type_sanity
runs early, so the table might need to include many types that are created
later, to avoid "false positives" in the associated test.
> But I'm not sure I believe
> that query. It's got hard-wired assumptions about which typtype values
> need to be covered. Why is it okay to exclude range and multirange?
> Are we sure that all composites are okay to exclude? Likewise, the
> restriction to pg_catalog and information_schema schemas seems likely to
> bite us someday. There are some very random exclusions based on name
> patterns, which seem unsafe (let's list the specific type OIDs), and
> again the nearby comments don't match the code. But the biggest issue
> is that this can only cover core datatypes, not any contrib stuff.
I changed to use regtype/OIDs, included range/multirange and stopped including
only pg_catalog/information_schema. But didn't yet handle composites.
> I don't know what we could do about contrib types. Maybe we should
> figure that covering core types is already a step forward, and be
> happy with getting that done.
Right .. this is meant to at least handle the lowest hanging fruit.
--
Justin
Attachments:v5-0001-WIP-pg_upgrade-test.sh-changes-needed-to-allow-te.patch (text/x-diff, 3.8K) download
From 79bed0997a1c720f103100697bdaa0cb1ee1261d Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 5 Dec 2020 22:31:19 -0600
Subject: [PATCH v5 1/4] WIP: pg_upgrade/test.sh: changes needed to allow
testing upgrade to v14dev from v9.5-v13
---
src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
1 file changed, 86 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.shindex 1ba326decd..9288cfdda8 100644--- a/src/bin/pg_upgrade/test.sh+++ b/src/bin/pg_upgrade/test.sh@@ -176,18 +176,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
esac
fix_sql="$fix_sql
DROP FUNCTION IF EXISTS
- public.oldstyle_length(integer, text); -- last in 9.6+ 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- DROP OPERATOR IF EXISTS -- last in v13- public.#@# (pg_catalog.int8, NONE),- public.#%# (pg_catalog.int8, NONE),- public.!=- (pg_catalog.int8, NONE),+ 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=$?
fi
- pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?+ 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
# update references to old source tree's regress.so etc
--
2.17.0
v5-0002-More-changes-needed-to-allow-upgrade-testing.patch (text/x-diff, 3.0K) download
From 8280518a897e3745ede459e31cc78c9e4b0efdbe Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Mon, 11 Jan 2021 21:41:16 -0600
Subject: [PATCH v5 2/4] More changes needed to allow upgrade testing:
These all seem to complicate use of pg_upgrade/test.sh:
e78900afd217fa3eaa77c51e23a94c1466af421c Create by default sql/ and expected/ for output directory in pg_regress
40b132c1afbb4b1494aa8e48cc35ec98d2b90777 In the pg_upgrade test suite, don't write to src/test/regress.
fc49e24fa69a15efacd5b8958115ed9c43c48f9a Make WAL segment size configurable at initdb time.
c37b3d08ca6873f9d4eaf24c72a90a550970cbb8 Allow group access on PGDATA
da9b580d89903fee871cf54845ffa2b26bda2e11 Refactor dir/file permissions
---
src/bin/pg_upgrade/test.sh | 29 ++++++++++++++++++++++-------
1 file changed, 22 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.shindex 9288cfdda8..74c29229ac 100644--- a/src/bin/pg_upgrade/test.sh+++ b/src/bin/pg_upgrade/test.sh@@ -23,7 +23,13 @@ standard_initdb() {
# To increase coverage of non-standard segment size and group access
# without increasing test runtime, run these tests with a custom setting.
# Also, specify "-A trust" explicitly to suppress initdb's warning.
- "$1" -N --wal-segsize 1 -g -A trust+ if [ -z "$oldsrc" ]+ then+ "$1" -N -A trust --wal-segsize 1 -g+ else+ "$1" -N -A trust+ fi+
if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
then
cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -106,6 +112,10 @@ outputdir="$temp_root/regress"
EXTRA_REGRESS_OPTS="$EXTRA_REGRESS_OPTS --outputdir=$outputdir"
export EXTRA_REGRESS_OPTS
mkdir "$outputdir"
+mkdir "$outputdir"/testtablespace++mkdir "$outputdir"/sql+mkdir "$outputdir"/expected
logdir=`pwd`/log
rm -rf "$logdir"
@@ -311,24 +321,29 @@ pg_upgrade $PG_UPGRADE_OPTS -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbindir" -p "
# make sure all directories and files have group permissions, on Unix hosts
# Windows hosts don't support Unix-y permissions.
case $testhost in
- MINGW*|CYGWIN*) ;;- *) if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then- echo "files in PGDATA with permission != 640";+ *)+ x=`find "$PGDATA" -type f ! -perm 600 ! -perm 640 -ls`+ if [ -n "$x" ]; then+ echo "files in PGDATA with permission NOT IN (600, 640)";+ echo "$x" |head
exit 1;
fi ;;
esac
case $testhost in
MINGW*|CYGWIN*) ;;
- *) if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then- echo "directories in PGDATA with permission != 750";+ *)+ x=`find "$PGDATA" -type d ! -perm 700 ! -perm 750 -ls`+ if [ "$x" ]; then+ echo "directories in PGDATA with permission NOT IN (700, 750)";+ echo "$x" |head
exit 1;
fi ;;
esac
pg_ctl start -l "$logdir/postmaster2.log" -o "$POSTMASTER_OPTS" -w
-pg_dumpall --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?+pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
pg_ctl -m fast stop
if [ -n "$pg_dumpall2_status" ]; then
--
2.17.0
v5-0003-pg_upgrade-test-to-exercise-binary-compatibility.patch (text/x-diff, 8.4K) download
From 787dd9cd5a2088c8e545691bf1d532466c8311d6 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 5 Dec 2020 17:20:09 -0600
Subject: [PATCH v5 3/4] pg_upgrade: test to exercise binary compatibility
Creating a table with columns of many different datatypes to notice if the
binary format is accidentally changed again, as happened at:
7c15cef86 Base information_schema.sql_identifier domain on name, not varchar.
I checked that if I cherry-pick to v11, and comment out
old_11_check_for_sql_identifier_data_type_usage(), then pg_upgrade/test.sh
detects the original problem:
pg_dump: error: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613
I understand the buildfarm has its own cross-version-upgrade test, which I
think would catch this on its own.
---
src/test/regress/expected/sanity_check.out | 1 +
src/test/regress/expected/type_sanity.out | 55 ++++++++++++++++++++++
src/test/regress/sql/type_sanity.sql | 54 +++++++++++++++++++++
3 files changed, 110 insertions(+)
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.outindex d9ce961be2..f67e3853ff 100644--- a/src/test/regress/expected/sanity_check.out+++ b/src/test/regress/expected/sanity_check.out@@ -69,6 +69,7 @@ line_tbl|f
log_table|f
lseg_tbl|f
main_table|f
+manytypes|f
mlparted|f
mlparted1|f
mlparted11|f
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.outindex 5480f979c6..9ae2922169 100644--- a/src/test/regress/expected/type_sanity.out+++ b/src/test/regress/expected/type_sanity.out@@ -674,3 +674,58 @@ WHERE p1.rngmultitypid IS NULL OR p1.rngmultitypid = 0;
----------+------------+---------------
(0 rows)
+-- Create a table with different data types, to exercise binary compatibility+-- during pg_upgrade test+CREATE TABLE manytypes AS SELECT+'(11,12)'::point, '(1,1),(2,2)'::line,+'((11,11),(12,12))'::lseg, '((11,11),(13,13))'::box,+'((11,12),(13,13),(14,14))'::path AS openedpath, '[(11,12),(13,13),(14,14)]'::path AS closedpath,+'((11,12),(13,13),(14,14))'::polygon, '1,1,1'::circle,+'today'::date, 'now'::time, 'now'::timestamp, 'now'::timetz, 'now'::timestamptz, '12 seconds'::interval,+'{"reason":"because"}'::json, '{"when":"now"}'::jsonb, '$.a[*] ? (@ > 2)'::jsonpath,+'127.0.0.1'::inet, '127.0.0.0/8'::cidr, '00:01:03:86:1c:ba'::macaddr8, '00:01:03:86:1c:ba'::macaddr,+2::int2, 4::int4, 8::int8, 4::float4, '8'::float8, pi()::numeric,+'foo'::"char", 'c'::bpchar, 'abc'::varchar, 'name'::name, 'txt'::text, true::bool,+E'\\xDEADBEEF'::bytea, B'10001'::bit, B'10001'::varbit AS varbit, '12.34'::money,+'abc'::refcursor,+'1 2'::int2vector, '1 2'::oidvector, format('%s=UC/%s', USER, USER)::aclitem,+'a fat cat sat on a mat and ate a fat rat'::tsvector, 'fat & rat'::tsquery,+'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid, '11'::xid8,+'pg_class'::regclass, 'regtype'::regtype type, 'pg_monitor'::regrole,+'pg_class'::regclass::oid, '(1,1)'::tid, '2'::xid, '3'::cid,+'10:20:10,14,15'::txid_snapshot, '10:20:10,14,15'::pg_snapshot, '16/B374D848'::pg_lsn,+1::information_schema.cardinal_number,+'l'::information_schema.character_data,+'n'::information_schema.sql_identifier,+'now'::information_schema.time_stamp,+'YES'::information_schema.yes_or_no,+'venus'::planets, 'i16'::insenum,+'(1,2)'::int4range, '{(1,2)}'::int4multirange,+'(3,4)'::int8range, '{(3,4)}'::int8multirange,+'(1,2)'::float8range, '{(1,2)}'::float8multirange,+'(3,4)'::numrange, '{(3,4)}'::nummultirange,+'(a,b)'::textrange, '{(a,b)}'::textmultirange,+'(12.34, 56.78)'::cashrange, '{(12.34, 56.78)}'::cashmultirange,+'(2020-01-02, 2021-02-03)'::daterange,+'{(2020-01-02, 2021-02-03)}'::datemultirange,+'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,+'{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tsmultirange,+'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,+'{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange,+arrayrange(ARRAY[1,2], ARRAY[2,1]),+arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1]));+-- And now a test on the previous test, checking that all core types are+-- included in this table+-- XXX or some other non-catalog table processed by pg_upgrade+SELECT oid, typname, typtype, typelem, typarray, typarray FROM pg_type t+WHERE typtype NOT IN ('p', 'c')+-- reg* which cannot be pg_upgraded+AND oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper', 'regoperator', 'regconfig', 'regdictionary', 'regnamespace', 'regcollation']::regtype[])+-- XML might be disabled at compile-time+AND oid != ALL(ARRAY['xml', 'gtsvector', 'pg_node_tree', 'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list']::regtype[])+AND NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray=t.oid) -- exclude arrays+AND NOT EXISTS (SELECT 1 FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attnum>0 AND a.attrelid='manytypes'::regclass);+ oid | typname | typtype | typelem | typarray | typarray +-----+---------+---------+---------+----------+----------+(0 rows)+diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sqlindex 4739aca84a..668fe92778 100644--- a/src/test/regress/sql/type_sanity.sql+++ b/src/test/regress/sql/type_sanity.sql@@ -495,3 +495,57 @@ WHERE pronargs != 2
SELECT p1.rngtypid, p1.rngsubtype, p1.rngmultitypid
FROM pg_range p1
WHERE p1.rngmultitypid IS NULL OR p1.rngmultitypid = 0;
++-- Create a table with different data types, to exercise binary compatibility+-- during pg_upgrade test++CREATE TABLE manytypes AS SELECT+'(11,12)'::point, '(1,1),(2,2)'::line,+'((11,11),(12,12))'::lseg, '((11,11),(13,13))'::box,+'((11,12),(13,13),(14,14))'::path AS openedpath, '[(11,12),(13,13),(14,14)]'::path AS closedpath,+'((11,12),(13,13),(14,14))'::polygon, '1,1,1'::circle,+'today'::date, 'now'::time, 'now'::timestamp, 'now'::timetz, 'now'::timestamptz, '12 seconds'::interval,+'{"reason":"because"}'::json, '{"when":"now"}'::jsonb, '$.a[*] ? (@ > 2)'::jsonpath,+'127.0.0.1'::inet, '127.0.0.0/8'::cidr, '00:01:03:86:1c:ba'::macaddr8, '00:01:03:86:1c:ba'::macaddr,+2::int2, 4::int4, 8::int8, 4::float4, '8'::float8, pi()::numeric,+'foo'::"char", 'c'::bpchar, 'abc'::varchar, 'name'::name, 'txt'::text, true::bool,+E'\\xDEADBEEF'::bytea, B'10001'::bit, B'10001'::varbit AS varbit, '12.34'::money,+'abc'::refcursor,+'1 2'::int2vector, '1 2'::oidvector, format('%s=UC/%s', USER, USER)::aclitem,+'a fat cat sat on a mat and ate a fat rat'::tsvector, 'fat & rat'::tsquery,+'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid, '11'::xid8,+'pg_class'::regclass, 'regtype'::regtype type, 'pg_monitor'::regrole,+'pg_class'::regclass::oid, '(1,1)'::tid, '2'::xid, '3'::cid,+'10:20:10,14,15'::txid_snapshot, '10:20:10,14,15'::pg_snapshot, '16/B374D848'::pg_lsn,+1::information_schema.cardinal_number,+'l'::information_schema.character_data,+'n'::information_schema.sql_identifier,+'now'::information_schema.time_stamp,+'YES'::information_schema.yes_or_no,+'venus'::planets, 'i16'::insenum,+'(1,2)'::int4range, '{(1,2)}'::int4multirange,+'(3,4)'::int8range, '{(3,4)}'::int8multirange,+'(1,2)'::float8range, '{(1,2)}'::float8multirange,+'(3,4)'::numrange, '{(3,4)}'::nummultirange,+'(a,b)'::textrange, '{(a,b)}'::textmultirange,+'(12.34, 56.78)'::cashrange, '{(12.34, 56.78)}'::cashmultirange,+'(2020-01-02, 2021-02-03)'::daterange,+'{(2020-01-02, 2021-02-03)}'::datemultirange,+'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,+'{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tsmultirange,+'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,+'{(2020-01-02 03:04:05, 2021-02-03 06:07:08)}'::tstzmultirange,+arrayrange(ARRAY[1,2], ARRAY[2,1]),+arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1]));++-- And now a test on the previous test, checking that all core types are+-- included in this table+-- XXX or some other non-catalog table processed by pg_upgrade+SELECT oid, typname, typtype, typelem, typarray, typarray FROM pg_type t+WHERE typtype NOT IN ('p', 'c')+-- reg* which cannot be pg_upgraded+AND oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper', 'regoperator', 'regconfig', 'regdictionary', 'regnamespace', 'regcollation']::regtype[])+-- XML might be disabled at compile-time+AND oid != ALL(ARRAY['xml', 'gtsvector', 'pg_node_tree', 'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list']::regtype[])+AND NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray=t.oid) -- exclude arrays+AND NOT EXISTS (SELECT 1 FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attnum>0 AND a.attrelid='manytypes'::regclass);--
2.17.0
v5-0004-Move-pg_upgrade-kludges-to-sql-script.patch (text/x-diff, 6.6K) download
From 7190f3b6b19cd23d8a6b2eca2374ac1e92d94d18 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
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 | 89 ++++++++++++++++++++++++++
src/bin/pg_upgrade/test.sh | 96 +----------------------------
2 files changed, 90 insertions(+), 95 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.sqlnew file mode 100644index 0000000000..3dadd6ea74--- /dev/null+++ b/src/bin/pg_upgrade/test-upgrade.sql@@ -0,0 +1,89 @@+-- This file has a bunch of kludges needed for upgrading testing across major versions++SELECT+ ver >= 804 AND ver <= 1100 AS fromv84v11,+ ver >= 905 AND ver <= 1300 AS fromv95v13,+ ver >= 906 AND ver <= 1300 AS fromv96v13,+ ver <= 80400 AS fromv84,+ ver <= 90500 AS fromv95,+ ver <= 90600 AS fromv96,+ ver <= 100000 AS fromv10,+ ver <= 110000 AS fromv11,+ ver <= 120000 AS fromv12,+ ver <= 130000 AS fromv13+ FROM (SELECT current_setting('server_version_num')::int/100 AS ver) AS v;+\gset++\if :fromv84+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 :fromv13+-- 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 :fromv10+-- commit 068503c76511cdb0080bab689662a20e86b9c845+DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;++-- 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 :fromv96v13+-- Various things removed for v14+DROP AGGREGATE first_el_agg_any(anyelement);+\endif++\if :fromv95v13+-- 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 :fromv84v11+\if :fromv11+-- 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++-- if [ "$newsrc" != "$oldsrc" ]; then+-- # update references to old source tree's regress.so etc+-- fix_sql=""+-- case $oldpgversion in+-- 804??)+-- fix_sql="UPDATE pg_proc SET probin = replace(probin::text, '$oldsrc', '$newsrc')::bytea WHERE probin LIKE '$oldsrc%';"+-- ;;+-- *)+-- fix_sql="UPDATE pg_proc SET probin = replace(probin, '$oldsrc', '$newsrc') WHERE probin LIKE '$oldsrc%';"+-- ;;+-- esac+-- psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?+--+-- mv "$temp_root"/dump1.sql "$temp_root"/dump1.sql.orig+-- sed "s;$oldsrc;$newsrc;g" "$temp_root"/dump1.sql.orig >"$temp_root"/dump1.sql+-- fidiff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.shindex 74c29229ac..a3df427f1d 100644--- a/src/bin/pg_upgrade/test.sh+++ b/src/bin/pg_upgrade/test.sh@@ -178,101 +178,7 @@ 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
--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, bruce@momjian.us, peter.eisentraut@enterprisedb.com, tomas.vondra@2ndquadrant.com, andres@anarazel.de, noah@leadboat.com
Subject: Re: pg_upgrade test for binary compatibility of core data types
In-Reply-To: <20210430183348.GQ27406@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox