From: Justin Pryzby Date: Sat, 5 Dec 2020 17:20:09 -0600 Subject: [PATCH v4 3/3] 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 | 39 ++++++++++++++++++++++ src/test/regress/sql/type_sanity.sql | 38 +++++++++++++++++++++ 3 files changed, 78 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..598a39ae03 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -672,3 +672,42 @@ 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, +'10:20:10,14,15'::txid_snapshot, '10:20:10,14,15'::pg_snapshot, +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..1df9859118 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -495,3 +495,41 @@ 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, +'10:20:10,14,15'::txid_snapshot, '10:20:10,14,15'::pg_snapshot, +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 --kadn00tgSopKmJ1H--