On 06.12.25 01:42, Paul A Jungwirth wrote:
transformForPortionOfClause():Using get_typname_and_namespace() to get the name of a range type and then using that to construct a function call of the same name is fragile. Also, it leads to unexpected error messages when the types don't match: DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM 1 TO 2; ERROR: function pg_catalog.daterange(integer, integer) does not exist Well, you cover that in the tests, but I don't think it's right. There should be a way to go into the catalogs and get the correct range constructor function for a range type using only OID references. Then you can build a FuncExpr node directly and don't need to go the detour of building a fake FuncCall node to transform. (You'd still need to transform the arguments separately in that case.)I added a function, get_range_constructor2, which I call to build a FuncExpr now. I got rid of get_typname_and_namespace. That said, looking up the constructor is tricky, because there isn't a direct oid lookup you can make. The rule is that it has the same name as the rangetype, with two args both matching the subtype. At least the rule is encapsulated now. And I think this function will be useful for the PERIODs patch, which needs similar don't-parse-your-own-node-trees work.
How about an alternative approach: We record the required constructor functions in the pg_range catalog, and then just look them up from there. I have put together a quick patch for this, see attached.
Maybe we don't need to record all of them. In particular, some of the multirange constructor functions seem to only exist to serve as cast functions. Do you foresee down the road needing to look up any other ones starting from the range type?
From 8c2684ee197c882bf035e5163c3be777cd63280c Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Thu, 8 Jan 2026 15:56:12 +0100 Subject: [PATCH] Record range constructor functions in pg_range --- src/backend/catalog/pg_range.c | 9 +++- src/backend/commands/typecmds.c | 32 +++++++++---- src/include/catalog/pg_range.dat | 12 +++++ src/include/catalog/pg_range.h | 13 +++++- src/test/regress/expected/oidjoins.out | 5 ++ src/test/regress/expected/type_sanity.out | 56 ++++++++++++++++++++++- src/test/regress/sql/type_sanity.sql | 44 +++++++++++++++++- 7 files changed, 157 insertions(+), 14 deletions(-) diff --git a/src/backend/catalog/pg_range.c b/src/backend/catalog/pg_range.c index cd21c84c8fd..3d194e67fbf 100644 --- a/src/backend/catalog/pg_range.c +++ b/src/backend/catalog/pg_range.c @@ -35,7 +35,9 @@ void RangeCreate(Oid rangeTypeOid, Oid rangeSubType, Oid rangeCollation, Oid rangeSubOpclass, RegProcedure rangeCanonical, - RegProcedure rangeSubDiff, Oid multirangeTypeOid) + RegProcedure rangeSubDiff, Oid multirangeTypeOid, + RegProcedure rangeConstr2, RegProcedure rangeConstr3, + RegProcedure multirangeConstr0, RegProcedure multirangeConstr1, RegProcedure multirangeConstr2) { Relation pg_range; Datum values[Natts_pg_range]; @@ -57,6 +59,11 @@ RangeCreate(Oid rangeTypeOid, Oid rangeSubType, Oid rangeCollation, values[Anum_pg_range_rngcanonical - 1] = ObjectIdGetDatum(rangeCanonical); values[Anum_pg_range_rngsubdiff - 1] = ObjectIdGetDatum(rangeSubDiff); values[Anum_pg_range_rngmultitypid - 1] = ObjectIdGetDatum(multirangeTypeOid); + values[Anum_pg_range_rngconstr2 - 1] = ObjectIdGetDatum(rangeConstr2); + values[Anum_pg_range_rngconstr3 - 1] = ObjectIdGetDatum(rangeConstr3); + values[Anum_pg_range_rngmconstr0 - 1] = ObjectIdGetDatum(multirangeConstr0); + values[Anum_pg_range_rngmconstr1 - 1] = ObjectIdGetDatum(multirangeConstr1); + values[Anum_pg_range_rngmconstr2 - 1] = ObjectIdGetDatum(multirangeConstr2); tup = heap_form_tuple(RelationGetDescr(pg_range), values, nulls); diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index e5fa0578889..0a92688b298 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -111,10 +111,12 @@ Oid binary_upgrade_next_mrng_pg_type_oid = InvalidOid; Oid binary_upgrade_next_mrng_array_pg_type_oid = InvalidOid; static void makeRangeConstructors(const char *name, Oid namespace, - Oid rangeOid, Oid subtype); + Oid rangeOid, Oid subtype, + Oid rangeConstrOids[]); static void makeMultirangeConstructors(const char *name, Oid namespace, Oid multirangeOid, Oid rangeOid, - Oid rangeArrayOid, Oid *castFuncOid); + Oid rangeArrayOid, Oid *castFuncOid, + Oid multirangeConstrOids[]); static Oid findTypeInputFunction(List *procname, Oid typeOid); static Oid findTypeOutputFunction(List *procname, Oid typeOid); static Oid findTypeReceiveFunction(List *procname, Oid typeOid); @@ -1406,6 +1408,8 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt) ListCell *lc; ObjectAddress address; ObjectAddress mltrngaddress PG_USED_FOR_ASSERTS_ONLY; + Oid rangeConstrOids[2]; + Oid multirangeConstrOids[3]; Oid castFuncOid; /* Convert list of names to a name and namespace */ @@ -1661,10 +1665,6 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt) InvalidOid); /* type's collation (ranges never have one) */ Assert(multirangeOid == mltrngaddress.objectId); - /* Create the entry in pg_range */ - RangeCreate(typoid, rangeSubtype, rangeCollation, rangeSubOpclass, - rangeCanonical, rangeSubtypeDiff, multirangeOid); - /* * Create the array type that goes with it. */ @@ -1746,10 +1746,16 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt) CommandCounterIncrement(); /* And create the constructor functions for this range type */ - makeRangeConstructors(typeName, typeNamespace, typoid, rangeSubtype); + makeRangeConstructors(typeName, typeNamespace, typoid, rangeSubtype, rangeConstrOids); makeMultirangeConstructors(multirangeTypeName, typeNamespace, multirangeOid, typoid, rangeArrayOid, - &castFuncOid); + &castFuncOid, multirangeConstrOids); + + /* Create the entry in pg_range */ + RangeCreate(typoid, rangeSubtype, rangeCollation, rangeSubOpclass, + rangeCanonical, rangeSubtypeDiff, multirangeOid, + rangeConstrOids[0], rangeConstrOids[1], + multirangeConstrOids[0], multirangeConstrOids[1], multirangeConstrOids[2]); /* Create cast from the range type to its multirange type */ CastCreate(typoid, multirangeOid, castFuncOid, InvalidOid, InvalidOid, @@ -1772,7 +1778,8 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt) */ static void makeRangeConstructors(const char *name, Oid namespace, - Oid rangeOid, Oid subtype) + Oid rangeOid, Oid subtype, + Oid rangeConstrOids[]) { static const char *const prosrc[2] = {"range_constructor2", "range_constructor3"}; @@ -1833,6 +1840,8 @@ makeRangeConstructors(const char *name, Oid namespace, * pg_dump depends on this choice to avoid dumping the constructors. */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + + rangeConstrOids[i] = myself.objectId; } } @@ -1848,7 +1857,7 @@ makeRangeConstructors(const char *name, Oid namespace, static void makeMultirangeConstructors(const char *name, Oid namespace, Oid multirangeOid, Oid rangeOid, Oid rangeArrayOid, - Oid *castFuncOid) + Oid *castFuncOid, Oid multirangeConstrOids[]) { ObjectAddress myself, referenced; @@ -1899,6 +1908,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, * depends on this choice to avoid dumping the constructors. */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + multirangeConstrOids[0] = myself.objectId; pfree(argtypes); /* @@ -1939,6 +1949,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, 0.0); /* prorows */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + multirangeConstrOids[1] = myself.objectId; pfree(argtypes); *castFuncOid = myself.objectId; @@ -1978,6 +1989,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, 0.0); /* prorows */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + multirangeConstrOids[2] = myself.objectId; pfree(argtypes); pfree(allParameterTypes); pfree(parameterModes); diff --git a/src/include/catalog/pg_range.dat b/src/include/catalog/pg_range.dat index 830971c4944..f1e46a9d830 100644 --- a/src/include/catalog/pg_range.dat +++ b/src/include/catalog/pg_range.dat @@ -14,21 +14,33 @@ { rngtypid => 'int4range', rngsubtype => 'int4', rngmultitypid => 'int4multirange', rngsubopc => 'btree/int4_ops', + rngconstr2 => 'int4range(int4,int4)', rngconstr3 => 'int4range(int4,int4,text)', + rngmconstr0 => 'int4multirange()', rngmconstr1 => 'int4multirange(int4range)', rngmconstr2 => 'int4multirange(_int4range)', rngcanonical => 'int4range_canonical', rngsubdiff => 'int4range_subdiff' }, { rngtypid => 'numrange', rngsubtype => 'numeric', rngmultitypid => 'nummultirange', rngsubopc => 'btree/numeric_ops', + rngconstr2 => 'numrange(numeric,numeric)', rngconstr3 => 'numrange(numeric,numeric,text)', + rngmconstr0 => 'nummultirange()', rngmconstr1 => 'nummultirange(numrange)', rngmconstr2 => 'nummultirange(_numrange)', rngcanonical => '-', rngsubdiff => 'numrange_subdiff' }, { rngtypid => 'tsrange', rngsubtype => 'timestamp', rngmultitypid => 'tsmultirange', rngsubopc => 'btree/timestamp_ops', + rngconstr2 => 'tsrange(timestamp,timestamp)', rngconstr3 => 'tsrange(timestamp,timestamp,text)', + rngmconstr0 => 'tsmultirange()', rngmconstr1 => 'tsmultirange(tsrange)', rngmconstr2 => 'tsmultirange(_tsrange)', rngcanonical => '-', rngsubdiff => 'tsrange_subdiff' }, { rngtypid => 'tstzrange', rngsubtype => 'timestamptz', rngmultitypid => 'tstzmultirange', rngsubopc => 'btree/timestamptz_ops', + rngconstr2 => 'tstzrange(timestamptz,timestamptz)', rngconstr3 => 'tstzrange(timestamptz,timestamptz,text)', + rngmconstr0 => 'tstzmultirange()', rngmconstr1 => 'tstzmultirange(tstzrange)', rngmconstr2 => 'tstzmultirange(_tstzrange)', rngcanonical => '-', rngsubdiff => 'tstzrange_subdiff' }, { rngtypid => 'daterange', rngsubtype => 'date', rngmultitypid => 'datemultirange', rngsubopc => 'btree/date_ops', + rngconstr2 => 'daterange(date,date)', rngconstr3 => 'daterange(date,date,text)', + rngmconstr0 => 'datemultirange()', rngmconstr1 => 'datemultirange(daterange)', rngmconstr2 => 'datemultirange(_daterange)', rngcanonical => 'daterange_canonical', rngsubdiff => 'daterange_subdiff' }, { rngtypid => 'int8range', rngsubtype => 'int8', rngmultitypid => 'int8multirange', rngsubopc => 'btree/int8_ops', + rngconstr2 => 'int8range(int8,int8)', rngconstr3 => 'int8range(int8,int8,text)', + rngmconstr0 => 'int8multirange()', rngmconstr1 => 'int8multirange(int8range)', rngmconstr2 => 'int8multirange(_int8range)', rngcanonical => 'int8range_canonical', rngsubdiff => 'int8range_subdiff' }, ] diff --git a/src/include/catalog/pg_range.h b/src/include/catalog/pg_range.h index 5b4f4615905..ad4d1e9187f 100644 --- a/src/include/catalog/pg_range.h +++ b/src/include/catalog/pg_range.h @@ -43,6 +43,15 @@ CATALOG(pg_range,3541,RangeRelationId) /* subtype's btree opclass */ Oid rngsubopc BKI_LOOKUP(pg_opclass); + /* range constructor functions */ + regproc rngconstr2 BKI_LOOKUP(pg_proc); + regproc rngconstr3 BKI_LOOKUP(pg_proc); + + /* multirange constructor functions */ + regproc rngmconstr0 BKI_LOOKUP(pg_proc); + regproc rngmconstr1 BKI_LOOKUP(pg_proc); + regproc rngmconstr2 BKI_LOOKUP(pg_proc); + /* canonicalize range, or 0 */ regproc rngcanonical BKI_LOOKUP_OPT(pg_proc); @@ -69,7 +78,9 @@ MAKE_SYSCACHE(RANGEMULTIRANGE, pg_range_rngmultitypid_index, 4); extern void RangeCreate(Oid rangeTypeOid, Oid rangeSubType, Oid rangeCollation, Oid rangeSubOpclass, RegProcedure rangeCanonical, - RegProcedure rangeSubDiff, Oid multirangeTypeOid); + RegProcedure rangeSubDiff, Oid multirangeTypeOid, + RegProcedure rangeConstr2, RegProcedure rangeConstr3, + RegProcedure multirangeConstr0, RegProcedure multirangeConstr1, RegProcedure multirangeConstr2); extern void RangeDelete(Oid rangeTypeOid); #endif /* PG_RANGE_H */ diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 215eb899be3..f2d731a3017 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -249,6 +249,11 @@ NOTICE: checking pg_range {rngsubtype} => pg_type {oid} NOTICE: checking pg_range {rngmultitypid} => pg_type {oid} NOTICE: checking pg_range {rngcollation} => pg_collation {oid} NOTICE: checking pg_range {rngsubopc} => pg_opclass {oid} +NOTICE: checking pg_range {rngconstr2} => pg_proc {oid} +NOTICE: checking pg_range {rngconstr3} => pg_proc {oid} +NOTICE: checking pg_range {rngmconstr0} => pg_proc {oid} +NOTICE: checking pg_range {rngmconstr1} => pg_proc {oid} +NOTICE: checking pg_range {rngmconstr2} => pg_proc {oid} NOTICE: checking pg_range {rngcanonical} => pg_proc {oid} NOTICE: checking pg_range {rngsubdiff} => pg_proc {oid} NOTICE: checking pg_transform {trftype} => pg_type {oid} diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index 9ddcacec6bf..1d941d20b6b 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -610,7 +610,9 @@ WHERE (is_catalog_text_unique_index_oid(indexrelid) <> -- Look for illegal values in pg_range fields. SELECT r.rngtypid, r.rngsubtype FROM pg_range as r -WHERE r.rngtypid = 0 OR r.rngsubtype = 0 OR r.rngsubopc = 0; +WHERE r.rngtypid = 0 OR r.rngsubtype = 0 OR r.rngsubopc = 0 + OR r.rngconstr2 = 0 OR r.rngconstr3 = 0 + OR r.rngmconstr0 = 0 OR r.rngmconstr1 = 0 OR r.rngmconstr2 = 0; rngtypid | rngsubtype ----------+------------ (0 rows) @@ -663,6 +665,58 @@ WHERE r.rngmultitypid IS NULL OR r.rngmultitypid = 0; ----------+------------+--------------- (0 rows) +-- check constructor function arguments and return types +-- proname and prosrc are not required but match what DefineRange() produces and serves to sanity-check the catalog entries for built-in types. +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngconstr2 JOIN pg_type t ON r.rngtypid = t.oid +WHERE p.pronargs != 2 + OR p.proargtypes[0] != r.rngsubtype OR p.proargtypes[1] != r.rngsubtype + OR p.prorettype != r.rngtypid + OR p.proname != t.typname OR p.prosrc != 'range_constructor2'; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngconstr3 JOIN pg_type t ON r.rngtypid = t.oid +WHERE p.pronargs != 3 + OR p.proargtypes[0] != r.rngsubtype OR p.proargtypes[1] != r.rngsubtype OR p.proargtypes[2] != 'pg_catalog.text'::regtype + OR p.prorettype != r.rngtypid + OR p.proname != t.typname OR p.prosrc != 'range_constructor3'; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr0 JOIN pg_type t ON r.rngmultitypid = t.oid +WHERE p.pronargs != 0 + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor0'; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr1 JOIN pg_type t ON r.rngmultitypid = t.oid +WHERE p.pronargs != 1 + OR p.proargtypes[0] != r.rngtypid + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor1'; + rngtypid | rngsubtype | proname +----------+------------+--------- +(0 rows) + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr2 JOIN pg_type t ON r.rngmultitypid = t.oid JOIN pg_type t2 ON r.rngtypid = t2.oid +WHERE p.pronargs != 1 + OR p.proargtypes[0] != t2.typarray + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor2'; + rngtypid | rngsubtype | proname +----------+------------+--------- +(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 diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql index c2496823d90..1a1bd3f14a7 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -451,7 +451,9 @@ CREATE FUNCTION is_catalog_text_unique_index_oid(oid) RETURNS bool SELECT r.rngtypid, r.rngsubtype FROM pg_range as r -WHERE r.rngtypid = 0 OR r.rngsubtype = 0 OR r.rngsubopc = 0; +WHERE r.rngtypid = 0 OR r.rngsubtype = 0 OR r.rngsubopc = 0 + OR r.rngconstr2 = 0 OR r.rngconstr3 = 0 + OR r.rngmconstr0 = 0 OR r.rngmconstr1 = 0 OR r.rngmconstr2 = 0; -- rngcollation should be specified iff subtype is collatable @@ -491,6 +493,46 @@ CREATE FUNCTION is_catalog_text_unique_index_oid(oid) RETURNS bool FROM pg_range r WHERE r.rngmultitypid IS NULL OR r.rngmultitypid = 0; +-- check constructor function arguments and return types +-- proname and prosrc are not required but match what DefineRange() produces and serves to sanity-check the catalog entries for built-in types. + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngconstr2 JOIN pg_type t ON r.rngtypid = t.oid +WHERE p.pronargs != 2 + OR p.proargtypes[0] != r.rngsubtype OR p.proargtypes[1] != r.rngsubtype + OR p.prorettype != r.rngtypid + OR p.proname != t.typname OR p.prosrc != 'range_constructor2'; + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngconstr3 JOIN pg_type t ON r.rngtypid = t.oid +WHERE p.pronargs != 3 + OR p.proargtypes[0] != r.rngsubtype OR p.proargtypes[1] != r.rngsubtype OR p.proargtypes[2] != 'pg_catalog.text'::regtype + OR p.prorettype != r.rngtypid + OR p.proname != t.typname OR p.prosrc != 'range_constructor3'; + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr0 JOIN pg_type t ON r.rngmultitypid = t.oid +WHERE p.pronargs != 0 + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor0'; + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr1 JOIN pg_type t ON r.rngmultitypid = t.oid +WHERE p.pronargs != 1 + OR p.proargtypes[0] != r.rngtypid + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor1'; + +SELECT r.rngtypid, r.rngsubtype, p.proname +FROM pg_range r JOIN pg_proc p ON p.oid = r.rngmconstr2 JOIN pg_type t ON r.rngmultitypid = t.oid JOIN pg_type t2 ON r.rngtypid = t2.oid +WHERE p.pronargs != 1 + OR p.proargtypes[0] != t2.typarray + OR p.prorettype != r.rngmultitypid + OR p.proname != t.typname OR p.prosrc != 'multirange_constructor2'; + + +-- ****************************************** + -- 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 -- 2.52.0
