Skip to content

Commit a999341

Browse files
michaelpqmichaelpq
committed
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
1 parent 8378dad commit a999341

File tree

3 files changed

+189
-0
lines changed

3 files changed

+189
-0
lines changed

‎src/test/regress/expected/sanity_check.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,7 @@ sql_sizing|f
183183
sql_sizing_profiles|f
184184
stud_emp|f
185185
student|f
186+
tab_core_types|f
186187
tableam_parted_a_heap2|f
187188
tableam_parted_b_heap2|f
188189
tableam_parted_c_heap2|f

‎src/test/regress/expected/type_sanity.out

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -629,3 +629,98 @@ WHERE pronargs != 2
629629
----------+------------+---------
630630
(0 rows)
631631

632+
-- Create a table that holds all the known in-core data types and leave it
633+
-- around so as pg_upgrade is able to test their binary compatibility.
634+
CREATE TABLE tab_core_types AS SELECT
635+
'(11,12)'::point,
636+
'(1,1),(2,2)'::line,
637+
'((11,11),(12,12))'::lseg,
638+
'((11,11),(13,13))'::box,
639+
'((11,12),(13,13),(14,14))'::path AS openedpath,
640+
'[(11,12),(13,13),(14,14)]'::path AS closedpath,
641+
'((11,12),(13,13),(14,14))'::polygon,
642+
'1,1,1'::circle,
643+
'today'::date,
644+
'now'::time,
645+
'now'::timestamp,
646+
'now'::timetz,
647+
'now'::timestamptz,
648+
'12 seconds'::interval,
649+
'{"reason":"because"}'::json,
650+
'{"when":"now"}'::jsonb,
651+
'$.a[*] ? (@ > 2)'::jsonpath,
652+
'127.0.0.1'::inet,
653+
'127.0.0.0/8'::cidr,
654+
'00:01:03:86:1c:ba'::macaddr8,
655+
'00:01:03:86:1c:ba'::macaddr,
656+
2::int2, 4::int4, 8::int8,
657+
4::float4, '8'::float8, pi()::numeric,
658+
'foo'::"char",
659+
'c'::bpchar,
660+
'abc'::varchar,
661+
'name'::name,
662+
'txt'::text,
663+
true::bool,
664+
E'\\xDEADBEEF'::bytea,
665+
B'10001'::bit,
666+
B'10001'::varbit AS varbit,
667+
'12.34'::money,
668+
'abc'::refcursor,
669+
'1 2'::int2vector,
670+
'1 2'::oidvector,
671+
format('%s=UC/%s', USER, USER)::aclitem,
672+
'a fat cat sat on a mat and ate a fat rat'::tsvector,
673+
'fat & rat'::tsquery,
674+
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
675+
'pg_class'::regclass,
676+
'regtype'::regtype,
677+
'pg_monitor'::regrole,
678+
'pg_class'::regclass::oid,
679+
'(1,1)'::tid, '2'::xid, '3'::cid,
680+
'10:20:10,14,15'::txid_snapshot,
681+
'16/B374D848'::pg_lsn,
682+
1::information_schema.cardinal_number,
683+
'l'::information_schema.character_data,
684+
'n'::information_schema.sql_identifier,
685+
'now'::information_schema.time_stamp,
686+
'YES'::information_schema.yes_or_no,
687+
'venus'::planets,
688+
'i16'::insenum,
689+
'(1,2)'::int4range,
690+
'(3,4)'::int8range,
691+
'(1,2)'::float8range,
692+
'(3,4)'::numrange,
693+
'(a,b)'::textrange,
694+
'(12.34, 56.78)'::cashrange,
695+
'(2020-01-02, 2021-02-03)'::daterange,
696+
'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,
697+
'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,
698+
arrayrange(ARRAY[1,2], ARRAY[2,1]);
699+
-- Sanity check on the previous table, checking that all core types are
700+
-- included in this table.
701+
SELECT oid, typname, typtype, typelem, typarray, typarray
702+
FROM pg_type t
703+
WHERE typtype NOT IN ('p', 'c') AND
704+
-- reg* types cannot be pg_upgraded, so discard them.
705+
oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
706+
'regoperator', 'regconfig', 'regdictionary',
707+
'regnamespace']::regtype[]) AND
708+
-- Discard types that do not accept input values as these cannot be
709+
-- tested easily.
710+
-- Note: XML might be disabled at compile-time.
711+
oid != ALL(ARRAY['gtsvector', 'pg_node_tree',
712+
'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list',
713+
'xml']::regtype[]) AND
714+
-- Discard arrays.
715+
NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray = t.oid)
716+
-- Exclude everything from the table created above. This checks
717+
-- that no in-core types are missing in tab_core_types.
718+
AND NOT EXISTS (SELECT 1
719+
FROM pg_attribute a
720+
WHERE a.atttypid=t.oid AND
721+
a.attnum > 0 AND
722+
a.attrelid='tab_core_types'::regclass);
723+
oid | typname | typtype | typelem | typarray | typarray
724+
-----+---------+---------+---------+----------+----------
725+
(0 rows)
726+

‎src/test/regress/sql/type_sanity.sql

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -465,3 +465,96 @@ FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff
465465
WHERE pronargs != 2
466466
OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype
467467
OR prorettype != 'pg_catalog.float8'::regtype;
468+
469+
-- Create a table that holds all the known in-core data types and leave it
470+
-- around so as pg_upgrade is able to test their binary compatibility.
471+
CREATE TABLE tab_core_types AS SELECT
472+
'(11,12)'::point,
473+
'(1,1),(2,2)'::line,
474+
'((11,11),(12,12))'::lseg,
475+
'((11,11),(13,13))'::box,
476+
'((11,12),(13,13),(14,14))'::path AS openedpath,
477+
'[(11,12),(13,13),(14,14)]'::path AS closedpath,
478+
'((11,12),(13,13),(14,14))'::polygon,
479+
'1,1,1'::circle,
480+
'today'::date,
481+
'now'::time,
482+
'now'::timestamp,
483+
'now'::timetz,
484+
'now'::timestamptz,
485+
'12 seconds'::interval,
486+
'{"reason":"because"}'::json,
487+
'{"when":"now"}'::jsonb,
488+
'$.a[*] ? (@ > 2)'::jsonpath,
489+
'127.0.0.1'::inet,
490+
'127.0.0.0/8'::cidr,
491+
'00:01:03:86:1c:ba'::macaddr8,
492+
'00:01:03:86:1c:ba'::macaddr,
493+
2::int2, 4::int4, 8::int8,
494+
4::float4, '8'::float8, pi()::numeric,
495+
'foo'::"char",
496+
'c'::bpchar,
497+
'abc'::varchar,
498+
'name'::name,
499+
'txt'::text,
500+
true::bool,
501+
E'\\xDEADBEEF'::bytea,
502+
B'10001'::bit,
503+
B'10001'::varbit AS varbit,
504+
'12.34'::money,
505+
'abc'::refcursor,
506+
'1 2'::int2vector,
507+
'1 2'::oidvector,
508+
format('%s=UC/%s', USER, USER)::aclitem,
509+
'a fat cat sat on a mat and ate a fat rat'::tsvector,
510+
'fat & rat'::tsquery,
511+
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
512+
'pg_class'::regclass,
513+
'regtype'::regtype,
514+
'pg_monitor'::regrole,
515+
'pg_class'::regclass::oid,
516+
'(1,1)'::tid, '2'::xid, '3'::cid,
517+
'10:20:10,14,15'::txid_snapshot,
518+
'16/B374D848'::pg_lsn,
519+
1::information_schema.cardinal_number,
520+
'l'::information_schema.character_data,
521+
'n'::information_schema.sql_identifier,
522+
'now'::information_schema.time_stamp,
523+
'YES'::information_schema.yes_or_no,
524+
'venus'::planets,
525+
'i16'::insenum,
526+
'(1,2)'::int4range,
527+
'(3,4)'::int8range,
528+
'(1,2)'::float8range,
529+
'(3,4)'::numrange,
530+
'(a,b)'::textrange,
531+
'(12.34, 56.78)'::cashrange,
532+
'(2020-01-02, 2021-02-03)'::daterange,
533+
'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tsrange,
534+
'(2020-01-02 03:04:05, 2021-02-03 06:07:08)'::tstzrange,
535+
arrayrange(ARRAY[1,2], ARRAY[2,1]);
536+
537+
-- Sanity check on the previous table, checking that all core types are
538+
-- included in this table.
539+
SELECT oid, typname, typtype, typelem, typarray, typarray
540+
FROM pg_type t
541+
WHERE typtype NOT IN ('p', 'c') AND
542+
-- reg* types cannot be pg_upgraded, so discard them.
543+
oid != ALL(ARRAY['regproc', 'regprocedure', 'regoper',
544+
'regoperator', 'regconfig', 'regdictionary',
545+
'regnamespace']::regtype[]) AND
546+
-- Discard types that do not accept input values as these cannot be
547+
-- tested easily.
548+
-- Note: XML might be disabled at compile-time.
549+
oid != ALL(ARRAY['gtsvector', 'pg_node_tree',
550+
'pg_ndistinct', 'pg_dependencies', 'pg_mcv_list',
551+
'xml']::regtype[]) AND
552+
-- Discard arrays.
553+
NOT EXISTS (SELECT 1 FROM pg_type u WHERE u.typarray = t.oid)
554+
-- Exclude everything from the table created above. This checks
555+
-- that no in-core types are missing in tab_core_types.
556+
AND NOT EXISTS (SELECT 1
557+
FROM pg_attribute a
558+
WHERE a.atttypid=t.oid AND
559+
a.attnum > 0 AND
560+
a.attrelid='tab_core_types'::regclass);

0 commit comments

Comments
 (0)