On Wed, Dec 16, 2020 at 11:22:23AM -0600, Justin Pryzby wrote:
> On Sun, Dec 06, 2020 at 12:02:48PM -0600, Justin Pryzby wrote:
> > I meant to notice if the binary format is accidentally changed again, which
> > was
> > what happened here:
> > 7c15cef86 Base information_schema.sql_identifier domain on name, not
> > varchar.
> >
> > I added a table to the regression tests so it's processed by pg_upgrade
> > tests,
> > run like:
> >
> > | time make -C src/bin/pg_upgrade check oldsrc=`pwd`/11
> > oldbindir=`pwd`/11/tmp_install/usr/local/pgsql/bin
>
> Per cfbot, this avoids testing ::xml (support for which may not be enabled)
> And also now tests oid types.
>
> I think the per-version hacks should be grouped by logical change, rather than
> by version. Which I've started doing here.
rebased on 6df7a9698bb036610c1e8c6d375e1be38cb26d5f
--
Justin
>From a1114c3db36891f169122383db136fd8fb47cb10 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 5 Dec 2020 22:31:19 -0600
Subject: [PATCH v3 1/2] WIP: pg_upgrade/test.sh: changes needed to allow
testing upgrade from v11
---
src/bin/pg_upgrade/test.sh | 92 ++++++++++++++++++++++++++++++++++----
1 file changed, 84 insertions(+), 8 deletions(-)
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 04aa7fd9f5..9733217535 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -23,7 +23,7 @@ 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
+ "$1" -N -A trust
if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
then
cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -108,6 +108,9 @@ export EXTRA_REGRESS_OPTS
mkdir "$outputdir"
mkdir "$outputdir"/testtablespace
+mkdir "$outputdir"/sql
+mkdir "$outputdir"/expected
+
logdir=`pwd`/log
rm -rf "$logdir"
mkdir "$logdir"
@@ -172,16 +175,83 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
fix_sql=""
case $oldpgversion in
804??)
- fix_sql="DROP FUNCTION public.myfunc(integer); DROP FUNCTION public.oldstyle_length(integer, text);"
+ fix_sql="$fix_sql DROP FUNCTION public.myfunc(integer);"
;;
- *)
- fix_sql="DROP FUNCTION public.oldstyle_length(integer, text);"
+ esac
+
+ # Removed in v10 commit 5ded4bd21
+ case $oldpgversion in
+ 804??|9????)
+ fix_sql="$fix_sql DROP FUNCTION public.oldstyle_length(integer, text);"
+ ;;
+ esac
+
+ # commit 068503c76511cdb0080bab689662a20e86b9c845
+ case $oldpgversion in
+ 10????) # XXX
+ fix_sql="$fix_sql DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+ ;;
+ esac
+
+ # commit db3af9feb19f39827e916145f88fa5eca3130cb2
+ case $oldpgversion in
+ 10????) # XXX
+ fix_sql="$fix_sql DROP FUNCTION boxarea(box);"
+ fix_sql="$fix_sql DROP FUNCTION funny_dup17();"
;;
esac
+
+ # commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+ case $oldpgversion in
+ 10????) # XXX
+ 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
+ 804??|9????|10????|11????|12????|13????)
+ # commit 76f412ab3
+ # This one is only needed for v11+ ??
+ # (see below for more operators removed that also apply to older versions)
+ fix_sql="$fix_sql DROP OPERATOR public.!=- (pg_catalog.int8, NONE);"
+ ;;
+ esac
+ case $oldpgversion in
+ 804??|9????|10????|11????|12????|13????)
+ # commit 76f412ab3
+ fix_sql="$fix_sql DROP OPERATOR public.#@# (pg_catalog.int8, NONE);"
+ fix_sql="$fix_sql DROP OPERATOR public.#%# (pg_catalog.int8, NONE);"
+ fix_sql="$fix_sql DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);"
+
+ # commit 9e38c2bb5 and 97f73a978
+ # fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+ fix_sql="$fix_sql DROP AGGREGATE array_cat_accum(anyarray);"
+ fix_sql="$fix_sql DROP AGGREGATE first_el_agg_any(anyelement);"
+
+ # 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=$?
+ 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
@@ -227,23 +297,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*) ;;
- *) if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then
+ *)
+ x=`find "$PGDATA" -type f -perm /127 -ls`
+ if [ -n "$x" ]; then
echo "files in PGDATA with permission != 640";
+ echo "$x" |head
exit 1;
fi ;;
esac
case $testhost in
MINGW*) ;;
- *) if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then
+ *)
+ x=`find "$PGDATA" -type d -perm /027 -ls`
+ if [ "$x" ]; then
echo "directories in PGDATA with permission != 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 40269cc927cbb723256b1e6995f7aeefcad6ceac Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 5 Dec 2020 17:20:09 -0600
Subject: [PATCH v3 2/2] pg_upgrade: test to exercise binary compatibility
Creating a table with columns of many different datatypes.
---
src/test/regress/expected/sanity_check.out | 1 +
src/test/regress/expected/type_sanity.out | 38 ++++++++++++++++++++++
src/test/regress/sql/type_sanity.sql | 37 +++++++++++++++++++++
3 files changed, 76 insertions(+)
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 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.out
index 0c74dc96a8..f3476bbf10 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -672,3 +672,41 @@ 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,
+'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_class'::regclass::oid, '(1,1)'::tid, '2'::xid, '3'::cid,
+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;
+-- And now a test on the previous test, checking that all core types are
+-- included in this table (or some other non-catalog table processed by pg_upgrade).
+SELECT typname, typtype, typelem, typarray, typarray FROM pg_type t
+WHERE typnamespace IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
+AND typtype IN ('b', 'e', 'd')
+-- reg* cannot be pg_upgraded
+AND NOT typname~'_|^char$|^reg'
+-- XML might be disabled at compile-time
+AND oid != ALL(ARRAY['gtsvector', 'xml']::regtype[])
+AND NOT EXISTS (SELECT * FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attnum>0 AND a.attrelid='manytypes'::regclass);
+ 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 4739aca84a..ef08e5d010 100644
--- a/src/test/regress/sql/type_sanity.sql
+++ b/src/test/regress/sql/type_sanity.sql
@@ -495,3 +495,40 @@ 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,
+'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_class'::regclass::oid, '(1,1)'::tid, '2'::xid, '3'::cid,
+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;
+
+-- And now a test on the previous test, checking that all core types are
+-- included in this table (or some other non-catalog table processed by pg_upgrade).
+SELECT typname, typtype, typelem, typarray, typarray FROM pg_type t
+WHERE typnamespace IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
+AND typtype IN ('b', 'e', 'd')
+-- reg* cannot be pg_upgraded
+AND NOT typname~'_|^char$|^reg'
+-- XML might be disabled at compile-time
+AND oid != ALL(ARRAY['gtsvector', 'xml']::regtype[])
+AND NOT EXISTS (SELECT * FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attnum>0 AND a.attrelid='manytypes'::regclass);
--
2.17.0
