Add table to regression tests for binary-compatibility checks in pg_upgrade

This commit adds to the main regression test suite a table with all
the in-core data types (some exceptions apply).  This table is not
dropped, so as pg_upgrade would be able to check the binary
compatibility of the types tracked in the table.  If a new type is added
in core, this part of the tests would need a refresh but the tests are
designed to fail if that were to happen.

As this is useful for upgrades and that these rely on the objects
created in the regression test suite of the old version upgraded from,
a backpatch down to 12 is done, which is the last point where a binary
incompatible change has been done (7c15cef).  This will hopefully be
enough to find out if something gets broken during the development of a
new version of Postgres, so as it is possible to take actions in
pg_upgrade itself in this case (like 0ccfc28 for sql_identifier).

An area that is not covered yet is related to external modules, which
may create their own types.  The testing infrastructure of pg_upgrade is
not integrated yet with the external modules stored in core
(src/test/modules/ or contrib/, all use the same database name for their
tests so there would be an overlap).  This could be improved in the
future.

Author: Justin Pryzby
Reviewed-by: Jacob Champion, Peter Eisentraut, Tom Lane, Michael Paquier
Discussion: https://postgr.es/m/20201206180248.GI24052@telsasoft.com
Backpatch-through: 12

diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index a64f96e102c3f6934fde2f0af6ce71e4a0e7bcfc..99e7bb4886beca4a72109239b6baab8f8f6d209c 100644 (file)
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -183,6 +183,7 @@ sql_parts|f
 sql_sizing|f
 stud_emp|f
 student|f
+tab_core_types|f
 tableam_parted_a_heap2|f
 tableam_parted_b_heap2|f
 tableam_parted_c_heap2|f

diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index f567fd378e79c9f5d362a4dc7035d51c090a6553..3ffd9d0d712ff69dfe210c8e2acd4c5f39a131e7 100644 (file)
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -674,3 +674,105 @@ WHERE p1.rngmultitypid IS NULL OR p1.rngmultitypid = 0;
 ----------+------------+---------------
 (0 rows)
 
+-- Create a table that holds all the known in-core data types and leave it
+-- around so as pg_upgrade is able to test their binary compatibility.
+CREATE TABLE tab_core_types 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]));
+-- Sanity check on the previous table, checking that all core types are
+-- included in this table.
+SELECT oid, typname, typtype, typelem, typarray, typarray
+  FROM pg_type t
+  WHERE typtype NOT IN ('p', 'c') AND
+    -- reg* types cannot be pg_upgraded, so discard them.
+    oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
+                     'regoperator', 'regconfig', 'regdictionary',
+                     'regnamespace', 'regcollation']::regtype[]) AND
+    -- Discard types that do not accept input values as these cannot be
+    -- tested easily.
+    -- Note: XML might be disabled at compile-time.
+    oid != ALL(ARRAY['gtsvector', 'pg_node_tree',
+                     'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list',
+                     'pg_brin_bloom_summary',
+                     'pg_brin_minmax_multi_summary', 'xml']::regtype[]) AND
+    -- Discard arrays.
+    NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray = t.oid)
+    -- Exclude everything from the table created above.  This checks
+    -- that no in-core types are missing in tab_core_types.
+    AND NOT EXISTS (SELECT 1
+                    FROM pg_attribute a
+                    WHERE a.atttypid=t.oid AND
+                          a.attnum > 0 AND
+                          a.attrelid='tab_core_types'::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 404c3a20432e577271bd364c9e92234b1f0b43bc..f92773b75ee82fedfa8705cb8baf9d9f107b89f0 100644 (file)
--- a/src/test/regress/sql/type_sanity.sql
+++ b/src/test/regress/sql/type_sanity.sql
@@ -495,3 +495,103 @@ 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 that holds all the known in-core data types and leave it
+-- around so as pg_upgrade is able to test their binary compatibility.
+CREATE TABLE tab_core_types 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]));
+
+-- Sanity check on the previous table, checking that all core types are
+-- included in this table.
+SELECT oid, typname, typtype, typelem, typarray, typarray
+  FROM pg_type t
+  WHERE typtype NOT IN ('p', 'c') AND
+    -- reg* types cannot be pg_upgraded, so discard them.
+    oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
+                     'regoperator', 'regconfig', 'regdictionary',
+                     'regnamespace', 'regcollation']::regtype[]) AND
+    -- Discard types that do not accept input values as these cannot be
+    -- tested easily.
+    -- Note: XML might be disabled at compile-time.
+    oid != ALL(ARRAY['gtsvector', 'pg_node_tree',
+                     'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list',
+                     'pg_brin_bloom_summary',
+                     'pg_brin_minmax_multi_summary', 'xml']::regtype[]) AND
+    -- Discard arrays.
+    NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray = t.oid)
+    -- Exclude everything from the table created above.  This checks
+    -- that no in-core types are missing in tab_core_types.
+    AND NOT EXISTS (SELECT 1
+                    FROM pg_attribute a
+                    WHERE a.atttypid=t.oid AND
+                          a.attnum > 0 AND
+                          a.attrelid='tab_core_types'::regclass);