On Fri, Jul 16, 2021 at 06:02:18PM +0000, Jacob Champion wrote:
> On Fri, 2021-04-30 at 13:33 -0500, Justin Pryzby wrote:
> > On Sat, Mar 06, 2021 at 03:01:43PM -0500, Tom Lane wrote:
> > > 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".
>
> Here are the differences I see on a first pass (without putting too
> much thought into how significant the differences are). Buildfarm code
> I'm comparing against is at [1].
>
> - Both versions drop @#@ and array_cat_accum, but the buildfarm
> additionally replaces them with a new operator and aggregate,
> respectively.
>
> - The buildfarm's dropping of table OIDs is probably more resilient,
> since it loops over pg_class looking for relhasoids.
These are all "translated" from test.sh, so follow its logic.
Maybe it should be improved, but that's separate from this patch - which is
already doing a few unrelated things.
> - The buildfarm adjusts pg_proc for the location of regress.so; I see
> there's a commented placeholder for this at the end of the psql script
> but it's not yet implemented.
I didn't understand why this was done here, but it turns out it has to be done
*after* calling pg_dump. So it has to stay where it is.
> - Some version ranges are different between the two. For example,
> abstime_/reltime_/tinterval_tbl are dropped by the buildfarm if the old
> version is < 9.3, while the psql script drops them for old versions <=
> 10.
This was an error. Thanks.
> - The buildfarm drops the public.=> operator for much older versions of
> Postgres. I assume we don't need that here.
> As an aside, I think the "fromv10" naming scheme for the "old version
> <= 10" condition is unintuitive. If the old version is e.g. 9.6, we're
> not upgrading "from 10".
I renamed the version vars - feel free to suggest something better.
I'll solicit suggestions what else to do to progresss these.
@Andrew: did you have any comment on this part ?
|Subject: buildfarm xversion diff
|Forking
https://www.postgresql.org/message-id/20210328231433.gi15...@telsasoft.com
|
|I gave suggestion how to reduce the "lines of diff" metric almost to nothing,
|allowing a very small "fudge factor", and which I think makes this a pretty
|good metric rather than a passable one.
--
Justin
>From 01debb48fe424e94cd2533d062c828ee6308d3c9 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
test like:
time make -C src/bin/pg_upgrade check oldsrc=`pwd`/11 oldbindir=`pwd`/11/tmp_install/usr/local/pgsql/bin
---
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.sh
index 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
>From 8f39f5b66bae4994e022008a90356b3a36466833 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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 | 27 +++++++++++++++++++++------
1 file changed, 21 insertions(+), 6 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 9288cfdda8..2bdd8c19de 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,9 @@ 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"
@@ -312,23 +321,29 @@ pg_upgrade $PG_UPGRADE_OPTS -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbindir" -p "
# 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
>From 9e710615e610f055de9ae4675704fd7429dd8155 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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 | 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
>From 8a7a2ac9aa73f06a2b3d413fc3712677012ae88a Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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.out
index 982b6aff53..551f35d59f 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.out
index f567fd378e..58013a8df3 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', 'pg_brin_bloom_summary', 'pg_brin_minmax_multi_summary']::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.sql
index 404c3a2043..e98191f01f 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', 'pg_brin_bloom_summary', 'pg_brin_minmax_multi_summary']::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
