From 4509033a00df5f49c42a21772d8d617efe83e549 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 16 Nov 2011 18:21:34 -0500 Subject: [PATCH] Code review for range-types catalog entries. Fix assorted infelicities, such as dependency on OIDs that aren't hardwired, as well as outright misdeclaration of daterange_canonical(), which resulted in crashes if you invoked it directly. Add some more regression tests to try to catch similar mistakes in future. --- src/include/catalog/catversion.h | 2 +- src/include/catalog/indexing.h | 2 +- src/include/catalog/pg_opclass.h | 23 +++++++---- src/include/catalog/pg_operator.h | 39 +++++++++--------- src/include/catalog/pg_proc.h | 50 +++++++++++------------ src/include/catalog/pg_range.h | 14 +++---- src/include/catalog/pg_type.h | 5 +-- src/test/regress/expected/rangetypes.out | 26 ++++++------ src/test/regress/expected/type_sanity.out | 50 ++++++++++++++++++++++- src/test/regress/sql/rangetypes.sql | 12 +++--- src/test/regress/sql/type_sanity.sql | 41 ++++++++++++++++++- 11 files changed, 179 insertions(+), 85 deletions(-) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 6e2a060a12..8739fabe3b 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201111141 +#define CATALOG_VERSION_NO 201111161 #endif diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h index 0bf2d4d640..2e5d2249bb 100644 --- a/src/include/catalog/indexing.h +++ b/src/include/catalog/indexing.h @@ -303,7 +303,7 @@ DECLARE_UNIQUE_INDEX(pg_extension_oid_index, 3080, on pg_extension using btree(o DECLARE_UNIQUE_INDEX(pg_extension_name_index, 3081, on pg_extension using btree(extname name_ops)); #define ExtensionNameIndexId 3081 -DECLARE_UNIQUE_INDEX(pg_range_rgntypid_index, 3542, on pg_range using btree(rngtypid oid_ops)); +DECLARE_UNIQUE_INDEX(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops)); #define RangeTypidIndexId 3542 /* last step of initialization script: build the indexes declared above */ diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h index 05ffa0384c..eecd3b63c5 100644 --- a/src/include/catalog/pg_opclass.h +++ b/src/include/catalog/pg_opclass.h @@ -85,7 +85,7 @@ typedef FormData_pg_opclass *Form_pg_opclass; * initial contents of pg_opclass * * Note: we hard-wire an OID only for a few entries that have to be explicitly - * referenced in the C code for bootstrapping purposes. The rest get OIDs + * referenced in the C code or in built-in catalog entries. The rest get OIDs * assigned on-the-fly during initdb. * ---------------- */ @@ -102,11 +102,13 @@ DATA(insert ( 403 char_ops PGNSP PGUID 429 18 t 0 )); DATA(insert ( 405 char_ops PGNSP PGUID 431 18 t 0 )); DATA(insert ( 403 cidr_ops PGNSP PGUID 1974 869 f 0 )); DATA(insert ( 405 cidr_ops PGNSP PGUID 1975 869 f 0 )); -DATA(insert ( 403 date_ops PGNSP PGUID 434 1082 t 0 )); +DATA(insert OID = 3122 ( 403 date_ops PGNSP PGUID 434 1082 t 0 )); +#define DATE_BTREE_OPS_OID 3122 DATA(insert ( 405 date_ops PGNSP PGUID 435 1082 t 0 )); DATA(insert ( 403 float4_ops PGNSP PGUID 1970 700 t 0 )); DATA(insert ( 405 float4_ops PGNSP PGUID 1971 700 t 0 )); -DATA(insert ( 403 float8_ops PGNSP PGUID 1970 701 t 0 )); +DATA(insert OID = 3123 ( 403 float8_ops PGNSP PGUID 1970 701 t 0 )); +#define FLOAT8_BTREE_OPS_OID 3123 DATA(insert ( 405 float8_ops PGNSP PGUID 1971 701 t 0 )); DATA(insert ( 403 inet_ops PGNSP PGUID 1974 869 t 0 )); DATA(insert ( 405 inet_ops PGNSP PGUID 1975 869 t 0 )); @@ -116,7 +118,8 @@ DATA(insert ( 405 int2_ops PGNSP PGUID 1977 21 t 0 )); DATA(insert OID = 1978 ( 403 int4_ops PGNSP PGUID 1976 23 t 0 )); #define INT4_BTREE_OPS_OID 1978 DATA(insert ( 405 int4_ops PGNSP PGUID 1977 23 t 0 )); -DATA(insert ( 403 int8_ops PGNSP PGUID 1976 20 t 0 )); +DATA(insert OID = 3124 ( 403 int8_ops PGNSP PGUID 1976 20 t 0 )); +#define INT8_BTREE_OPS_OID 3124 DATA(insert ( 405 int8_ops PGNSP PGUID 1977 20 t 0 )); DATA(insert ( 403 interval_ops PGNSP PGUID 1982 1186 t 0 )); DATA(insert ( 405 interval_ops PGNSP PGUID 1983 1186 t 0 )); @@ -131,7 +134,8 @@ DATA(insert ( 405 macaddr_ops PGNSP PGUID 1985 829 t 0 )); */ DATA(insert ( 403 name_ops PGNSP PGUID 1986 19 t 2275 )); DATA(insert ( 405 name_ops PGNSP PGUID 1987 19 t 0 )); -DATA(insert ( 403 numeric_ops PGNSP PGUID 1988 1700 t 0 )); +DATA(insert OID = 3125 ( 403 numeric_ops PGNSP PGUID 1988 1700 t 0 )); +#define NUMERIC_BTREE_OPS_OID 3125 DATA(insert ( 405 numeric_ops PGNSP PGUID 1998 1700 t 0 )); DATA(insert OID = 1981 ( 403 oid_ops PGNSP PGUID 1989 26 t 0 )); #define OID_BTREE_OPS_OID 1981 @@ -139,18 +143,21 @@ DATA(insert ( 405 oid_ops PGNSP PGUID 1990 26 t 0 )); DATA(insert ( 403 oidvector_ops PGNSP PGUID 1991 30 t 0 )); DATA(insert ( 405 oidvector_ops PGNSP PGUID 1992 30 t 0 )); DATA(insert ( 403 record_ops PGNSP PGUID 2994 2249 t 0 )); -DATA(insert ( 403 text_ops PGNSP PGUID 1994 25 t 0 )); +DATA(insert OID = 3126 ( 403 text_ops PGNSP PGUID 1994 25 t 0 )); +#define TEXT_BTREE_OPS_OID 3126 DATA(insert ( 405 text_ops PGNSP PGUID 1995 25 t 0 )); DATA(insert ( 403 time_ops PGNSP PGUID 1996 1083 t 0 )); DATA(insert ( 405 time_ops PGNSP PGUID 1997 1083 t 0 )); -DATA(insert ( 403 timestamptz_ops PGNSP PGUID 434 1184 t 0 )); +DATA(insert OID = 3127 ( 403 timestamptz_ops PGNSP PGUID 434 1184 t 0 )); +#define TIMESTAMPTZ_BTREE_OPS_OID 3127 DATA(insert ( 405 timestamptz_ops PGNSP PGUID 1999 1184 t 0 )); DATA(insert ( 403 timetz_ops PGNSP PGUID 2000 1266 t 0 )); DATA(insert ( 405 timetz_ops PGNSP PGUID 2001 1266 t 0 )); DATA(insert ( 403 varbit_ops PGNSP PGUID 2002 1562 t 0 )); DATA(insert ( 403 varchar_ops PGNSP PGUID 1994 25 f 0 )); DATA(insert ( 405 varchar_ops PGNSP PGUID 1995 25 f 0 )); -DATA(insert ( 403 timestamp_ops PGNSP PGUID 434 1114 t 0 )); +DATA(insert OID = 3128 ( 403 timestamp_ops PGNSP PGUID 434 1114 t 0 )); +#define TIMESTAMP_BTREE_OPS_OID 3128 DATA(insert ( 405 timestamp_ops PGNSP PGUID 2040 1114 t 0 )); DATA(insert ( 403 text_pattern_ops PGNSP PGUID 2095 25 f 0 )); DATA(insert ( 403 varchar_pattern_ops PGNSP PGUID 2095 25 f 0 )); diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index f587f5b198..050239725e 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1674,32 +1674,33 @@ DATA(insert OID = 3886 ( ">=" PGNSP PGUID b f f 3831 3831 16 3885 3884 range DESCR("greater than or equal"); DATA(insert OID = 3887 ( ">" PGNSP PGUID b f f 3831 3831 16 3884 3885 range_gt scalargtsel scalargtjoinsel )); DESCR("greater than"); -DATA(insert OID = 3888 ( "&&" PGNSP PGUID b f f 3831 3831 16 3888 0 3857 - - )); +DATA(insert OID = 3888 ( "&&" PGNSP PGUID b f f 3831 3831 16 3888 0 range_overlaps - - )); DESCR("overlaps"); -DATA(insert OID = 3889 ( "@>" PGNSP PGUID b f f 3831 2776 16 3891 0 3858 - - )); +DATA(insert OID = 3889 ( "@>" PGNSP PGUID b f f 3831 2776 16 3891 0 range_contains_elem - - )); DESCR("contains"); -DATA(insert OID = 3890 ( "@>" PGNSP PGUID b f f 3831 3831 16 3892 0 3859 - - )); +DATA(insert OID = 3890 ( "@>" PGNSP PGUID b f f 3831 3831 16 3892 0 range_contains - - )); DESCR("contains"); -DATA(insert OID = 3891 ( "<@" PGNSP PGUID b f f 2776 3831 16 3889 0 3860 - - )); -DESCR("contained by"); -DATA(insert OID = 3892 ( "<@" PGNSP PGUID b f f 3831 3831 16 3890 0 3861 - - )); -DESCR("contained by"); -DATA(insert OID = 3893 ( "<<" PGNSP PGUID b f f 3831 3831 16 0 0 before scalarltsel scalarltjoinsel )); -DESCR("left of"); -DATA(insert OID = 3894 ( ">>" PGNSP PGUID b f f 3831 3831 16 0 0 after scalargtsel scalargtjoinsel )); -DESCR("right of"); -DATA(insert OID = 3895 ( "&<" PGNSP PGUID b f f 3831 3831 16 0 0 overleft scalarltsel scalarltjoinsel )); -DESCR("overlaps to left"); -DATA(insert OID = 3896 ( "&>" PGNSP PGUID b f f 3831 3831 16 0 0 overright scalargtsel scalargtjoinsel )); -DESCR("overlaps to right"); -DATA(insert OID = 3897 ( "-|-" PGNSP PGUID b f f 3831 3831 16 3897 0 adjacent - - )); -DESCR("adjacent"); +DATA(insert OID = 3891 ( "<@" PGNSP PGUID b f f 2776 3831 16 3889 0 elem_contained_by_range - - )); +DESCR("is contained by"); +DATA(insert OID = 3892 ( "<@" PGNSP PGUID b f f 3831 3831 16 3890 0 range_contained_by - - )); +DESCR("is contained by"); +DATA(insert OID = 3893 ( "<<" PGNSP PGUID b f f 3831 3831 16 0 0 range_before scalarltsel scalarltjoinsel )); +DESCR("is left of"); +DATA(insert OID = 3894 ( ">>" PGNSP PGUID b f f 3831 3831 16 0 0 range_after scalargtsel scalargtjoinsel )); +DESCR("is right of"); +DATA(insert OID = 3895 ( "&<" PGNSP PGUID b f f 3831 3831 16 0 0 range_overleft scalarltsel scalarltjoinsel )); +DESCR("overlaps or is left of"); +DATA(insert OID = 3896 ( "&>" PGNSP PGUID b f f 3831 3831 16 0 0 range_overright scalargtsel scalargtjoinsel )); +DESCR("overlaps or is right of"); +DATA(insert OID = 3897 ( "-|-" PGNSP PGUID b f f 3831 3831 16 3897 0 range_adjacent - - )); +DESCR("is adjacent to"); DATA(insert OID = 3898 ( "+" PGNSP PGUID b f f 3831 3831 3831 3898 0 range_union - - )); DESCR("range union"); -DATA(insert OID = 3899 ( "-" PGNSP PGUID b f f 3831 3831 3831 0 0 minus - - )); +DATA(insert OID = 3899 ( "-" PGNSP PGUID b f f 3831 3831 3831 0 0 range_minus - - )); DESCR("range difference"); DATA(insert OID = 3900 ( "*" PGNSP PGUID b f f 3831 3831 3831 3900 0 range_intersect - - )); -DESCR("intersection"); +DESCR("range intersection"); + /* * function prototypes diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 3b654ff7c4..eb5df3c231 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4334,23 +4334,23 @@ DESCR("fetch the last row value"); DATA(insert OID = 3114 ( nth_value PGNSP PGUID 12 1 0 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ )); DESCR("fetch the Nth row value"); -/* procs for range types */ +/* functions for range types */ DATA(insert OID = 3832 ( anyrange_in PGNSP PGUID 12 1 0 0 0 f f f t f s 3 0 3831 "2275 26 23" _null_ _null_ _null_ _null_ anyrange_in _null_ _null_ _null_ )); DESCR("I/O"); DATA(insert OID = 3833 ( anyrange_out PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 2275 "3831" _null_ _null_ _null_ _null_ anyrange_out _null_ _null_ _null_ )); DESCR("I/O"); -DATA(insert OID = 3834 ( range_in PGNSP PGUID 12 1 0 0 0 f f f t f s 3 0 3831 "2275 26 23" _null_ _null_ _null_ _null_ range_in _null_ _null_ _null_ )); +DATA(insert OID = 3834 ( range_in PGNSP PGUID 12 1 0 0 0 f f f t f s 3 0 3831 "2275 26 23" _null_ _null_ _null_ _null_ range_in _null_ _null_ _null_ )); DESCR("I/O"); -DATA(insert OID = 3835 ( range_out PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 2275 "3831" _null_ _null_ _null_ _null_ range_out _null_ _null_ _null_ )); +DATA(insert OID = 3835 ( range_out PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 2275 "3831" _null_ _null_ _null_ _null_ range_out _null_ _null_ _null_ )); DESCR("I/O"); DATA(insert OID = 3836 ( range_recv PGNSP PGUID 12 1 0 0 0 f f f t f s 3 0 3831 "2281 26 23" _null_ _null_ _null_ _null_ range_recv _null_ _null_ _null_ )); DESCR("I/O"); DATA(insert OID = 3837 ( range_send PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 17 "3831" _null_ _null_ _null_ _null_ range_send _null_ _null_ _null_ )); DESCR("I/O"); -DATA(insert OID = 3848 ( lower PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 2283 "3831" _null_ _null_ _null_ _null_ range_lower _null_ _null_ _null_ )); -DESCR("return the range's lower bound"); -DATA(insert OID = 3849 ( upper PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 2283 "3831" _null_ _null_ _null_ _null_ range_upper _null_ _null_ _null_ )); -DESCR("return the range's upper bound"); +DATA(insert OID = 3848 ( lower PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 2283 "3831" _null_ _null_ _null_ _null_ range_lower _null_ _null_ _null_ )); +DESCR("lower bound of range"); +DATA(insert OID = 3849 ( upper PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 2283 "3831" _null_ _null_ _null_ _null_ range_upper _null_ _null_ _null_ )); +DESCR("upper bound of range"); DATA(insert OID = 3850 ( isempty PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 16 "3831" _null_ _null_ _null_ _null_ range_empty _null_ _null_ _null_ )); DESCR("is the range empty?"); DATA(insert OID = 3851 ( lower_inc PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 16 "3831" _null_ _null_ _null_ _null_ range_lower_inc _null_ _null_ _null_ )); @@ -4365,31 +4365,31 @@ DATA(insert OID = 3855 ( range_eq PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "38 DESCR("implementation of = operator"); DATA(insert OID = 3856 ( range_ne PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_ne _null_ _null_ _null_ )); DESCR("implementation of <> operator"); -DATA(insert OID = 3857 ( overlaps PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overlaps _null_ _null_ _null_ )); +DATA(insert OID = 3857 ( range_overlaps PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overlaps _null_ _null_ _null_ )); DESCR("implementation of && operator"); -DATA(insert OID = 3858 ( contains PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 2776" _null_ _null_ _null_ _null_ range_contains_elem _null_ _null_ _null_ )); +DATA(insert OID = 3858 ( range_contains_elem PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 2776" _null_ _null_ _null_ _null_ range_contains_elem _null_ _null_ _null_ )); DESCR("implementation of @> operator"); -DATA(insert OID = 3859 ( contains PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_contains _null_ _null_ _null_ )); +DATA(insert OID = 3859 ( range_contains PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_contains _null_ _null_ _null_ )); DESCR("implementation of @> operator"); -DATA(insert OID = 3860 ( contained_by PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "2776 3831" _null_ _null_ _null_ _null_ elem_contained_by_range _null_ _null_ _null_ )); +DATA(insert OID = 3860 ( elem_contained_by_range PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "2776 3831" _null_ _null_ _null_ _null_ elem_contained_by_range _null_ _null_ _null_ )); DESCR("implementation of <@ operator"); -DATA(insert OID = 3861 ( contained_by PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_contained_by _null_ _null_ _null_ )); +DATA(insert OID = 3861 ( range_contained_by PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_contained_by _null_ _null_ _null_ )); DESCR("implementation of <@ operator"); -DATA(insert OID = 3862 ( adjacent PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_adjacent _null_ _null_ _null_ )); +DATA(insert OID = 3862 ( range_adjacent PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_adjacent _null_ _null_ _null_ )); DESCR("implementation of -|- operator"); -DATA(insert OID = 3863 ( before PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_before _null_ _null_ _null_ )); +DATA(insert OID = 3863 ( range_before PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_before _null_ _null_ _null_ )); DESCR("implementation of << operator"); -DATA(insert OID = 3864 ( after PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_after _null_ _null_ _null_ )); +DATA(insert OID = 3864 ( range_after PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_after _null_ _null_ _null_ )); DESCR("implementation of >> operator"); -DATA(insert OID = 3865 ( overleft PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overleft _null_ _null_ _null_ )); +DATA(insert OID = 3865 ( range_overleft PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overleft _null_ _null_ _null_ )); DESCR("implementation of &< operator"); -DATA(insert OID = 3866 ( overright PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overright _null_ _null_ _null_ )); +DATA(insert OID = 3866 ( range_overright PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_overright _null_ _null_ _null_ )); DESCR("implementation of &> operator"); -DATA(insert OID = 3867 ( range_union PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ range_union _null_ _null_ _null_ )); +DATA(insert OID = 3867 ( range_union PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ range_union _null_ _null_ _null_ )); DESCR("implementation of + operator"); DATA(insert OID = 3868 ( range_intersect PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ range_intersect _null_ _null_ _null_ )); DESCR("implementation of * operator"); -DATA(insert OID = 3869 ( minus PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ range_minus _null_ _null_ _null_ )); +DATA(insert OID = 3869 ( range_minus PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ range_minus _null_ _null_ _null_ )); DESCR("implementation of - operator"); DATA(insert OID = 3870 ( range_cmp PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 23 "3831 3831" _null_ _null_ _null_ _null_ range_cmp _null_ _null_ _null_ )); DESCR("less-equal-greater"); @@ -4397,9 +4397,9 @@ DATA(insert OID = 3871 ( range_lt PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "38 DATA(insert OID = 3872 ( range_le PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_le _null_ _null_ _null_ )); DATA(insert OID = 3873 ( range_ge PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_ge _null_ _null_ _null_ )); DATA(insert OID = 3874 ( range_gt PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "3831 3831" _null_ _null_ _null_ _null_ range_gt _null_ _null_ _null_ )); -DATA(insert OID = 3875 ( range_gist_consistent PGNSP PGUID 12 1 0 0 0 f f f t f i 5 0 16 "2281 3831 21 26 2281" _null_ _null_ _null_ _null_ range_gist_consistent _null_ _null_ _null_ )); +DATA(insert OID = 3875 ( range_gist_consistent PGNSP PGUID 12 1 0 0 0 f f f t f i 5 0 16 "2281 3831 23 26 2281" _null_ _null_ _null_ _null_ range_gist_consistent _null_ _null_ _null_ )); DESCR("GiST support"); -DATA(insert OID = 3876 ( range_gist_union PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ range_gist_union _null_ _null_ _null_ )); +DATA(insert OID = 3876 ( range_gist_union PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ range_gist_union _null_ _null_ _null_ )); DESCR("GiST support"); DATA(insert OID = 3877 ( range_gist_compress PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ range_gist_compress _null_ _null_ _null_ )); DESCR("GiST support"); @@ -4409,15 +4409,15 @@ DATA(insert OID = 3879 ( range_gist_penalty PGNSP PGUID 12 1 0 0 0 f f f t f i DESCR("GiST support"); DATA(insert OID = 3880 ( range_gist_picksplit PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ range_gist_picksplit _null_ _null_ _null_ )); DESCR("GiST support"); -DATA(insert OID = 3881 ( range_gist_same PGNSP PGUID 12 1 0 0 0 f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ range_gist_same _null_ _null_ _null_ )); +DATA(insert OID = 3881 ( range_gist_same PGNSP PGUID 12 1 0 0 0 f f f t f i 3 0 2281 "3831 3831 2281" _null_ _null_ _null_ _null_ range_gist_same _null_ _null_ _null_ )); DESCR("GiST support"); -DATA(insert OID = 3902 ( hash_range PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 23 "3831" _null_ _null_ _null_ _null_ hash_range _null_ _null_ _null_ )); +DATA(insert OID = 3902 ( hash_range PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 23 "3831" _null_ _null_ _null_ _null_ hash_range _null_ _null_ _null_ )); DESCR("hash a range"); DATA(insert OID = 3914 ( int4range_canonical PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 3904 "3904" _null_ _null_ _null_ _null_ int4range_canonical _null_ _null_ _null_ )); DESCR("convert an int4 range to canonical form"); DATA(insert OID = 3928 ( int8range_canonical PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 3926 "3926" _null_ _null_ _null_ _null_ int8range_canonical _null_ _null_ _null_ )); DESCR("convert an int8 range to canonical form"); -DATA(insert OID = 3915 ( daterange_canonical PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 3906 "3906" _null_ _null_ _null_ _null_ daterange_canonical _null_ _null_ _null_ )); +DATA(insert OID = 3915 ( daterange_canonical PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 3912 "3912" _null_ _null_ _null_ _null_ daterange_canonical _null_ _null_ _null_ )); DESCR("convert a date range to canonical form"); DATA(insert OID = 3922 ( int4range_subdiff PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 701 "23 23" _null_ _null_ _null_ _null_ int4range_subdiff _null_ _null_ _null_ )); DESCR("float8 difference of two int4 values"); @@ -4432,7 +4432,6 @@ DESCR("float8 difference of two timestamp values"); DATA(insert OID = 3930 ( tstzrange_subdiff PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 701 "1184 1184" _null_ _null_ _null_ _null_ tstzrange_subdiff _null_ _null_ _null_ )); DESCR("float8 difference of two timestamp with time zone values"); - DATA(insert OID = 3838 ( int4range PGNSP PGUID 12 1 0 0 0 f f f f f i 0 0 3904 "" _null_ _null_ _null_ _null_ range_constructor0 _null_ _null_ _null_ )); DESCR("int4range constructor"); DATA(insert OID = 3839 ( int4range PGNSP PGUID 12 1 0 0 0 f f f f f i 1 0 3904 "23" _null_ _null_ _null_ _null_ range_constructor1 _null_ _null_ _null_ )); @@ -4482,6 +4481,7 @@ DESCR("int8range constructor"); DATA(insert OID = 3946 ( int8range PGNSP PGUID 12 1 0 0 0 f f f f f i 3 0 3926 "20 20 25" _null_ _null_ _null_ _null_ range_constructor3 _null_ _null_ _null_ )); DESCR("int8range constructor"); + /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, diff --git a/src/include/catalog/pg_range.h b/src/include/catalog/pg_range.h index 3826976e82..cc9ba293c1 100644 --- a/src/include/catalog/pg_range.h +++ b/src/include/catalog/pg_range.h @@ -38,7 +38,7 @@ CATALOG(pg_range,3541) BKI_WITHOUT_OIDS Oid rngcollation; /* collation for this range type, or 0 */ Oid rngsubopc; /* subtype's btree opclass */ regproc rngcanonical; /* canonicalize range, or 0 */ - regproc rngsubdiff; /* subtype difference as a float8 (for GiST) */ + regproc rngsubdiff; /* subtype difference as a float8, or 0 */ } FormData_pg_range; /* ---------------- @@ -65,12 +65,12 @@ typedef FormData_pg_range *Form_pg_range; * initial contents of pg_range * ---------------- */ -DATA(insert ( 3904 23 0 1978 int4range_canonical int4range_subdiff)); -DATA(insert ( 3906 1700 0 10037 - numrange_subdiff)); -DATA(insert ( 3908 1114 0 10054 - tsrange_subdiff)); -DATA(insert ( 3910 1184 0 10047 - tstzrange_subdiff)); -DATA(insert ( 3912 1082 0 10019 daterange_canonical daterange_subdiff)); -DATA(insert ( 3926 20 0 10029 int8range_canonical int8range_subdiff)); +DATA(insert ( 3904 23 0 1978 int4range_canonical int4range_subdiff)); +DATA(insert ( 3906 1700 0 3125 - numrange_subdiff)); +DATA(insert ( 3908 1114 0 3128 - tsrange_subdiff)); +DATA(insert ( 3910 1184 0 3127 - tstzrange_subdiff)); +DATA(insert ( 3912 1082 0 3122 daterange_canonical daterange_subdiff)); +DATA(insert ( 3926 20 0 3124 int8range_canonical int8range_subdiff)); /* diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index a062d1e248..1806985b69 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -592,7 +592,6 @@ DESCR("txid snapshot"); DATA(insert OID = 2949 ( _txid_snapshot PGNSP PGUID -1 f b A f t \054 0 2970 0 array_in array_out array_recv array_send - - - d x f 0 -1 0 0 _null_ _null_ )); /* range types */ - DATA(insert OID = 3904 ( int4range PGNSP PGUID -1 f r R f t \054 0 0 3905 range_in range_out range_recv range_send - - - i x f 0 -1 0 0 _null_ _null_ )); DESCR("range of integers"); #define INT4RANGEOID 3904 @@ -653,8 +652,8 @@ DATA(insert OID = 2776 ( anynonarray PGNSP PGUID 4 t p P f t \054 0 0 0 anynona DATA(insert OID = 3500 ( anyenum PGNSP PGUID 4 t p P f t \054 0 0 0 anyenum_in anyenum_out - - - - - i p f 0 -1 0 0 _null_ _null_ )); #define ANYENUMOID 3500 DATA(insert OID = 3115 ( fdw_handler PGNSP PGUID 4 t p P f t \054 0 0 0 fdw_handler_in fdw_handler_out - - - - - i p f 0 -1 0 0 _null_ _null_ )); -#define FDW_HANDLEROID 3115 -DATA(insert OID = 3831 ( anyrange PGNSP PGUID 4 t p P f t \054 0 0 0 anyrange_in anyrange_out - - - - - i p f 0 -1 0 0 _null_ _null_ )); +#define FDW_HANDLEROID 3115 +DATA(insert OID = 3831 ( anyrange PGNSP PGUID -1 f p P f t \054 0 0 0 anyrange_in anyrange_out - - - - - d x f 0 -1 0 0 _null_ _null_ )); #define ANYRANGEOID 3831 diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index b2258b9045..ba18272ff0 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -175,7 +175,7 @@ SELECT lower_inc(nr), lower(nr), upper(nr), upper_inc(nr) FROM numrange_test t | 1.7 | 1.7 | t (2 rows) -SELECT * FROM numrange_test WHERE contains(nr, numrange(1.9,1.91)); +SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); nr ----------- (,) @@ -189,7 +189,7 @@ SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1); (,) (1 row) -SELECT * FROM numrange_test WHERE contained_by(numrange(-1e7,-10000.1), nr); +SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr); nr ------ (,) @@ -229,9 +229,9 @@ select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); t (1 row) -select adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); - adjacent ----------- +select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); + range_adjacent +---------------- f (1 row) @@ -247,9 +247,9 @@ select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); t (1 row) -select adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); - adjacent ----------- +select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); + range_adjacent +---------------- t (1 row) @@ -283,15 +283,15 @@ select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0); [1.1,2.0) (1 row) -select minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); - minus +select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); + range_minus ------------- [10.1,12.2] (1 row) -select minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); - minus -------- +select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); + range_minus +------------- empty (1 row) diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index a159ac718a..19d437ade5 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -1,7 +1,7 @@ -- -- TYPE_SANITY -- Sanity checks for common errors in making type-related system tables: --- pg_type, pg_class, pg_attribute. +-- pg_type, pg_class, pg_attribute, pg_range. -- -- None of the SELECTs here should ever find any matching entries, -- so the expected output is easy to maintain ;-). @@ -367,3 +367,51 @@ WHERE p1.atttypid = p2.oid AND ----------+---------+-----+--------- (0 rows) +-- **************** pg_range **************** +-- Look for illegal values in pg_range fields. +SELECT p1.rngtypid, p1.rngsubtype +FROM pg_range as p1 +WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0; + rngtypid | rngsubtype +----------+------------ +(0 rows) + +-- rngcollation should be specified iff subtype is collatable +SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation +FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype +WHERE (rngcollation = 0) != (typcollation = 0); + rngtypid | rngsubtype | rngcollation | typcollation +----------+------------+--------------+-------------- +(0 rows) + +-- opclass had better be a btree opclass accepting the subtype. +-- We must allow anyarray matches, cf opr_sanity's binary_coercible() +SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname +FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc +WHERE o.opcmethod != 403 OR + ((o.opcintype != p1.rngsubtype) AND NOT + (o.opcintype = 'pg_catalog.anyarray'::regtype AND + EXISTS(select 1 from pg_catalog.pg_type where + oid = p1.rngsubtype and typelem != 0 and typlen = -1))); + rngtypid | rngsubtype | opcmethod | opcname +----------+------------+-----------+--------- +(0 rows) + +-- canonical function, if any, had better match the range type +SELECT p1.rngtypid, p1.rngsubtype, p.proname +FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical +WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + +-- subdiff function, if any, had better match the subtype +SELECT p1.rngtypid, p1.rngsubtype, p.proname +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; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index 4b455f1d35..4e122a36cc 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -54,9 +54,9 @@ SELECT isempty(nr) FROM numrange_test; SELECT lower_inc(nr), lower(nr), upper(nr), upper_inc(nr) FROM numrange_test WHERE NOT isempty(nr) AND NOT lower_inf(nr) AND NOT upper_inf(nr); -SELECT * FROM numrange_test WHERE contains(nr, numrange(1.9,1.91)); +SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1); -SELECT * FROM numrange_test WHERE contained_by(numrange(-1e7,-10000.1), nr); +SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr); SELECT * FROM numrange_test WHERE 1.9 <@ nr; SELECT * FROM numrange_test WHERE nr = 'empty'; SELECT * FROM numrange_test WHERE range_eq(nr, '(1.1, 2.2)'); @@ -65,10 +65,10 @@ SELECT * FROM numrange_test WHERE nr = '[1.1, 2.2)'; select numrange(2.0, 1.0); select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); -select adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); +select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()'); select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); -select adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); +select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); select numrange(1.1, 3.3) <@ numrange(0.1,10.1); select numrange(0.1, 10.1) <@ numrange(1.1,3.3); @@ -76,8 +76,8 @@ select numrange(0.1, 10.1) <@ numrange(1.1,3.3); select numrange(1.1, 2.2) - numrange(2.0, 3.0); select numrange(1.1, 2.2) - numrange(2.2, 3.0); select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0); -select minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); -select minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); +select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); +select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); select numrange(1.0, 2.0) << numrange(3.0, 4.0); diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql index 2ed03f39bc..d7d9cea5dc 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -1,7 +1,7 @@ -- -- TYPE_SANITY -- Sanity checks for common errors in making type-related system tables: --- pg_type, pg_class, pg_attribute. +-- pg_type, pg_class, pg_attribute, pg_range. -- -- None of the SELECTs here should ever find any matching entries, -- so the expected output is easy to maintain ;-). @@ -285,3 +285,42 @@ WHERE p1.atttypid = p2.oid AND p1.attalign != p2.typalign OR p1.attbyval != p2.typbyval OR (p1.attstorage != p2.typstorage AND p1.attstorage != 'p')); + +-- **************** pg_range **************** + +-- Look for illegal values in pg_range fields. + +SELECT p1.rngtypid, p1.rngsubtype +FROM pg_range as p1 +WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0; + +-- rngcollation should be specified iff subtype is collatable + +SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation +FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype +WHERE (rngcollation = 0) != (typcollation = 0); + +-- opclass had better be a btree opclass accepting the subtype. +-- We must allow anyarray matches, cf opr_sanity's binary_coercible() + +SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname +FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc +WHERE o.opcmethod != 403 OR + ((o.opcintype != p1.rngsubtype) AND NOT + (o.opcintype = 'pg_catalog.anyarray'::regtype AND + EXISTS(select 1 from pg_catalog.pg_type where + oid = p1.rngsubtype and typelem != 0 and typlen = -1))); + +-- canonical function, if any, had better match the range type + +SELECT p1.rngtypid, p1.rngsubtype, p.proname +FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical +WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid; + +-- subdiff function, if any, had better match the subtype + +SELECT p1.rngtypid, p1.rngsubtype, p.proname +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; -- 2.30.2