From 7cdcf46561948a2011a88945bac7d05cb1f13baa Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 5 Dec 2020 17:20:09 -0600 Subject: [PATCH 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 | 42 ++++++++++++++++++++++ src/test/regress/sql/type_sanity.sql | 42 ++++++++++++++++++++++ 3 files changed, 85 insertions(+) diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 192445878d..aa0a4fd9be 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 274130e706..2feefc7224 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -631,3 +631,45 @@ WHERE pronargs != 2 ----------+------------+--------- (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, +'bar'::xml, +'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, 'english'::regconfig, 'simple'::regdictionary, 'pg_catalog'::regnamespace, +-- -- 'POSIX'::regcollation, +-- -- '+'::regoper, +-- '*(integer,integer)'::regoperator, +-- -- 'sum'::regproc, +-- 'sum(int4)'::regprocedure, USER::regrole, 'regtype'::regtype type, +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') +AND NOT typname~'_|^char$|^reg' +AND oid != ALL(ARRAY['gtsvector', 'regcollation', 'regoper', 'regproc']::regtype[]) +AND NOT EXISTS (SELECT * FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attrelid='manytypes'::regclass) +ORDER BY 1,2,3,4; + 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 4b492ce062..f2b490a9c6 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -467,3 +467,45 @@ FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff WHERE pronargs != 2 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype OR prorettype != 'pg_catalog.float8'::regtype; + +-- 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, +'bar'::xml, +'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, 'english'::regconfig, 'simple'::regdictionary, 'pg_catalog'::regnamespace, +-- -- 'POSIX'::regcollation, +-- -- '+'::regoper, +-- '*(integer,integer)'::regoperator, +-- -- 'sum'::regproc, +-- 'sum(int4)'::regprocedure, USER::regrole, 'regtype'::regtype type, +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') +AND NOT typname~'_|^char$|^reg' +AND oid != ALL(ARRAY['gtsvector', 'regcollation', 'regoper', 'regproc']::regtype[]) +AND NOT EXISTS (SELECT * FROM pg_attribute a WHERE a.atttypid=t.oid AND a.attrelid='manytypes'::regclass) +ORDER BY 1,2,3,4; -- 2.17.0