public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Eisentraut <[email protected]>
To: Paul A Jungwirth <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 Application Time Update & Delete
Date: Mon, 19 Jan 2026 14:37:43 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
References: <[email protected]>
<CA+renyUiEu2yTHk+Vy-Rt2xA5Vnp2N_ohN=npiNmMKTh53qDfA@mail.gmail.com>
<[email protected]>
<CA+renyW7ZB_k9AgmSFJU2EegL9r1k1sgWo4-9tGGkgwxNqe6kw@mail.gmail.com>
<CA+renyUodzxAvMnpa_LTvo+Ru1ZKH+Su8KaPvD4iMtguFKzq4g@mail.gmail.com>
<[email protected]>
<CA+renyU-iz_zvM0gGP=dvBPVrz=Jj3qdCjtAh5nLZRhb49xMFw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+renyXchHgpMbYX-cR8fuNnnpf_+FJ6PkHoXoa2AgzRnz4vxQ@mail.gmail.com>
<[email protected]>
<CA+renyXH3AF6JVzZGVcT5mAo=0QncB-MpWJeqb2JG66sgyq09g@mail.gmail.com>
<[email protected]>
<CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
<[email protected]>
<CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
On 10.01.26 07:16, Paul A Jungwirth wrote:
> We would need to document these columns.
Done that.
> The C code uses `mltrng` a lot. Do we want to use that here? I don't
> see it in the catalog yet, but it seems clearer than `rngm`. I guess
> we have to start with `rng` though. We have `rngmultitypid`, so maybe
> `rngmulticonstr0`? Okay I understand why you went with `rngm`.
I tuned the naming again in the new patch. I changed "constr" to
"construct" because "constr" read too much like "constraint" to me. I
also did a bit of "mtlrng". I think it's a bit more consistent and less
ambiguous now.
> It's tempting to use two oidvectors, one for range constructors and
> another for multirange, with the 0-arg constructor in position 0,
> 1-arg in position 1, etc. We could use InvalidOid to say there is no
> such constructor. So we would have rngconstr of `{0,0,123,456}` and
> mltrngconstr of `{123,456,789}`. But is it better to avoid varlena
> columns if we can?
I don't think oidvectors would be appropriate here. These are for when
you have a group of values that you need together, like for function
arguments. But here we want to access them separately. And it would
create a lot of notational and a bit of storage overhead.
I had in the previous patch used some arrays as arguments in the
internal functions, but in the second patch I'm also getting rid of that
because it's uselessly inconsistent.
> ```
> 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);
> ```
>
> Is there a reason you're adding them in the middle of the struct? It
> doesn't help with packing.
Well, initially I had done that so that the edits to pg_range.dat are
easier. But I think this order makes some sense, because it has the
mandatory data first and then the optional data later. But it doesn't
matter much either way.
> This needs some kind of pg_upgrade support I assume? It will have to
> work for user-defined rangetypes too.
No, I don't think there needs to be pg_upgrade support. Existing range
types are dumped as CREATE TYPE ... RANGE commands, and when those get
restored it will create the new catalog entries.
From 82a087573dd60765321abb7904de271404e59e6f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 19 Jan 2026 13:54:41 +0100
Subject: [PATCH v2] Record range constructor functions in pg_range
When a range type is created, several construction functions are also
created, two for the range type and three for the multirange type.
These have an internal dependency, so they "belong" to the range type.
But there was no way to identify those functions when given a range
type. An upcoming patch needs access to the two- or possibly the
three-argument range constructor function for a given range type. The
only way to do that would be with fragile workarounds like matching
names and argument types. The correct way to do that kind of thing is
to record to the links in the system catalogs. This is what this
patch does, it records the OIDs of these five constructor functions in
the pg_range catalog. (Currently, there is no code that makes use of
this.)
Reviewed-by: Paul A Jungwirth <[email protected]>
Discussion: https://www.postgresql.org/message-id/7d63ddfa-c735-4dfe-8c7a-4f1e2a621058%40eisentraut.org
TODO: catversion
---
doc/src/sgml/catalogs.sgml | 54 +++++++++++++++++++++
src/backend/catalog/pg_range.c | 9 +++-
src/backend/commands/typecmds.c | 48 +++++++++++++-----
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 | 59 ++++++++++++++++++++++-
src/test/regress/sql/type_sanity.sql | 47 +++++++++++++++++-
8 files changed, 230 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2fc63442980..332193565e2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6676,6 +6676,60 @@ <title><structname>pg_range</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngconstruct2</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 2-argument range constructor function (lower and upper)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngconstruct3</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 3-argument range constructor function (lower, upper, and
+ flags)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct0</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 0-argument multirange constructor function (constructs empty
+ range)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct1</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 1-argument multirange constructor function (constructs
+ multirange from single range, also used as cast function)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct2</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 2-argument multirange constructor function (constructs
+ multirange from array of ranges)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>rngcanonical</structfield> <type>regproc</type>
diff --git a/src/backend/catalog/pg_range.c b/src/backend/catalog/pg_range.c
index cd21c84c8fd..cb8c79d0e83 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 rangeConstruct2, RegProcedure rangeConstruct3,
+ RegProcedure mltrngConstruct0, RegProcedure mltrngConstruct1, RegProcedure mltrngConstruct2)
{
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_rngconstruct2 - 1] = ObjectIdGetDatum(rangeConstruct2);
+ values[Anum_pg_range_rngconstruct3 - 1] = ObjectIdGetDatum(rangeConstruct3);
+ values[Anum_pg_range_rngmltconstruct0 - 1] = ObjectIdGetDatum(mltrngConstruct0);
+ values[Anum_pg_range_rngmltconstruct1 - 1] = ObjectIdGetDatum(mltrngConstruct1);
+ values[Anum_pg_range_rngmltconstruct2 - 1] = ObjectIdGetDatum(mltrngConstruct2);
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..288edb25f2f 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 *rangeConstruct2_p, Oid *rangeConstruct3_p);
static void makeMultirangeConstructors(const char *name, Oid namespace,
Oid multirangeOid, Oid rangeOid,
- Oid rangeArrayOid, Oid *castFuncOid);
+ Oid rangeArrayOid,
+ Oid *mltrngConstruct0_p, Oid *mltrngConstruct1_p, Oid *mltrngConstruct2_p);
static Oid findTypeInputFunction(List *procname, Oid typeOid);
static Oid findTypeOutputFunction(List *procname, Oid typeOid);
static Oid findTypeReceiveFunction(List *procname, Oid typeOid);
@@ -1406,6 +1408,11 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
ListCell *lc;
ObjectAddress address;
ObjectAddress mltrngaddress PG_USED_FOR_ASSERTS_ONLY;
+ Oid rangeConstruct2Oid = InvalidOid;
+ Oid rangeConstruct3Oid = InvalidOid;
+ Oid mltrngConstruct0Oid = InvalidOid;
+ Oid mltrngConstruct1Oid = InvalidOid;
+ Oid mltrngConstruct2Oid = InvalidOid;
Oid castFuncOid;
/* Convert list of names to a name and namespace */
@@ -1661,10 +1668,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 +1749,18 @@ 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,
+ &rangeConstruct2Oid, &rangeConstruct3Oid);
makeMultirangeConstructors(multirangeTypeName, typeNamespace,
multirangeOid, typoid, rangeArrayOid,
- &castFuncOid);
+ &mltrngConstruct0Oid, &mltrngConstruct1Oid, &mltrngConstruct2Oid);
+ castFuncOid = mltrngConstruct1Oid;
+
+ /* Create the entry in pg_range */
+ RangeCreate(typoid, rangeSubtype, rangeCollation, rangeSubOpclass,
+ rangeCanonical, rangeSubtypeDiff, multirangeOid,
+ rangeConstruct2Oid, rangeConstruct3Oid,
+ mltrngConstruct0Oid, mltrngConstruct1Oid, mltrngConstruct2Oid);
/* Create cast from the range type to its multirange type */
CastCreate(typoid, multirangeOid, castFuncOid, InvalidOid, InvalidOid,
@@ -1769,10 +1780,14 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
*
* We actually define 2 functions, with 2 through 3 arguments. This is just
* to offer more convenience for the user.
+ *
+ * The OIDs of the created functions are returned through the pointer
+ * arguments.
*/
static void
makeRangeConstructors(const char *name, Oid namespace,
- Oid rangeOid, Oid subtype)
+ Oid rangeOid, Oid subtype,
+ Oid *rangeConstruct2_p, Oid *rangeConstruct3_p)
{
static const char *const prosrc[2] = {"range_constructor2",
"range_constructor3"};
@@ -1833,6 +1848,11 @@ makeRangeConstructors(const char *name, Oid namespace,
* pg_dump depends on this choice to avoid dumping the constructors.
*/
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+
+ if (pronargs[i] == 2)
+ *rangeConstruct2_p = myself.objectId;
+ else if (pronargs[i] == 3)
+ *rangeConstruct3_p = myself.objectId;
}
}
@@ -1842,13 +1862,13 @@ makeRangeConstructors(const char *name, Oid namespace,
* If we had an anyrangearray polymorphic type we could use it here,
* but since each type has its own constructor name there's no need.
*
- * Sets castFuncOid to the oid of the new constructor that can be used
- * to cast from a range to a multirange.
+ * The OIDs of the created functions are returned through the pointer
+ * arguments.
*/
static void
makeMultirangeConstructors(const char *name, Oid namespace,
Oid multirangeOid, Oid rangeOid, Oid rangeArrayOid,
- Oid *castFuncOid)
+ Oid *mltrngConstruct0_p, Oid *mltrngConstruct1_p, Oid *mltrngConstruct2_p)
{
ObjectAddress myself,
referenced;
@@ -1899,6 +1919,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
* depends on this choice to avoid dumping the constructors.
*/
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct0_p = myself.objectId;
pfree(argtypes);
/*
@@ -1939,8 +1960,8 @@ makeMultirangeConstructors(const char *name, Oid namespace,
0.0); /* prorows */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct1_p = myself.objectId;
pfree(argtypes);
- *castFuncOid = myself.objectId;
/* n-arg constructor - vararg */
argtypes = buildoidvector(&rangeArrayOid, 1);
@@ -1978,6 +1999,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
0.0); /* prorows */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct2_p = 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..fa5e6ff0c3e 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',
+ rngconstruct2 => 'int4range(int4,int4)', rngconstruct3 => 'int4range(int4,int4,text)',
+ rngmltconstruct0 => 'int4multirange()', rngmltconstruct1 => 'int4multirange(int4range)', rngmltconstruct2 => 'int4multirange(_int4range)',
rngcanonical => 'int4range_canonical', rngsubdiff => 'int4range_subdiff' },
{ rngtypid => 'numrange', rngsubtype => 'numeric',
rngmultitypid => 'nummultirange', rngsubopc => 'btree/numeric_ops',
+ rngconstruct2 => 'numrange(numeric,numeric)', rngconstruct3 => 'numrange(numeric,numeric,text)',
+ rngmltconstruct0 => 'nummultirange()', rngmltconstruct1 => 'nummultirange(numrange)', rngmltconstruct2 => 'nummultirange(_numrange)',
rngcanonical => '-', rngsubdiff => 'numrange_subdiff' },
{ rngtypid => 'tsrange', rngsubtype => 'timestamp',
rngmultitypid => 'tsmultirange', rngsubopc => 'btree/timestamp_ops',
+ rngconstruct2 => 'tsrange(timestamp,timestamp)', rngconstruct3 => 'tsrange(timestamp,timestamp,text)',
+ rngmltconstruct0 => 'tsmultirange()', rngmltconstruct1 => 'tsmultirange(tsrange)', rngmltconstruct2 => 'tsmultirange(_tsrange)',
rngcanonical => '-', rngsubdiff => 'tsrange_subdiff' },
{ rngtypid => 'tstzrange', rngsubtype => 'timestamptz',
rngmultitypid => 'tstzmultirange', rngsubopc => 'btree/timestamptz_ops',
+ rngconstruct2 => 'tstzrange(timestamptz,timestamptz)', rngconstruct3 => 'tstzrange(timestamptz,timestamptz,text)',
+ rngmltconstruct0 => 'tstzmultirange()', rngmltconstruct1 => 'tstzmultirange(tstzrange)', rngmltconstruct2 => 'tstzmultirange(_tstzrange)',
rngcanonical => '-', rngsubdiff => 'tstzrange_subdiff' },
{ rngtypid => 'daterange', rngsubtype => 'date',
rngmultitypid => 'datemultirange', rngsubopc => 'btree/date_ops',
+ rngconstruct2 => 'daterange(date,date)', rngconstruct3 => 'daterange(date,date,text)',
+ rngmltconstruct0 => 'datemultirange()', rngmltconstruct1 => 'datemultirange(daterange)', rngmltconstruct2 => 'datemultirange(_daterange)',
rngcanonical => 'daterange_canonical', rngsubdiff => 'daterange_subdiff' },
{ rngtypid => 'int8range', rngsubtype => 'int8',
rngmultitypid => 'int8multirange', rngsubopc => 'btree/int8_ops',
+ rngconstruct2 => 'int8range(int8,int8)', rngconstruct3 => 'int8range(int8,int8,text)',
+ rngmltconstruct0 => 'int8multirange()', rngmltconstruct1 => 'int8multirange(int8range)', rngmltconstruct2 => '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..32ee8cf43a0 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 rngconstruct2 BKI_LOOKUP(pg_proc);
+ regproc rngconstruct3 BKI_LOOKUP(pg_proc);
+
+ /* multirange constructor functions */
+ regproc rngmltconstruct0 BKI_LOOKUP(pg_proc);
+ regproc rngmltconstruct1 BKI_LOOKUP(pg_proc);
+ regproc rngmltconstruct2 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 rangeConstruct2, RegProcedure rangeConstruct3,
+ RegProcedure mltrngConstruct0, RegProcedure mltrngConstruct1, RegProcedure mltrngConstruct2);
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..25aaae8d05a 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 {rngconstruct2} => pg_proc {oid}
+NOTICE: checking pg_range {rngconstruct3} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct0} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct1} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct2} => 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..1d21d3eb446 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.rngconstruct2 = 0 OR r.rngconstruct3 = 0
+ OR r.rngmltconstruct0 = 0 OR r.rngmltconstruct1 = 0 OR r.rngmltconstruct2 = 0;
rngtypid | rngsubtype
----------+------------
(0 rows)
@@ -663,6 +665,61 @@ WHERE r.rngmultitypid IS NULL OR r.rngmultitypid = 0;
----------+------------+---------------
(0 rows)
+-- check constructor function arguments and return types
+--
+-- proname and prosrc are not required to have these particular
+-- values, but this matches 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.rngconstruct2 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.rngconstruct3 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.rngmltconstruct0 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.rngmltconstruct1 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.rngmltconstruct2 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..95d5b6e0915 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.rngconstruct2 = 0 OR r.rngconstruct3 = 0
+ OR r.rngmltconstruct0 = 0 OR r.rngmltconstruct1 = 0 OR r.rngmltconstruct2 = 0;
-- rngcollation should be specified iff subtype is collatable
@@ -491,6 +493,49 @@ 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 to have these particular
+-- values, but this matches 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.rngconstruct2 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.rngconstruct3 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.rngmltconstruct0 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.rngmltconstruct1 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.rngmltconstruct2 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
base-commit: 34740b90bc123d645a3a71231b765b778bdcf049
--
2.52.0
Attachments:
[text/plain] v2-0001-Record-range-constructor-functions-in-pg_range.patch (22.6K, 2-v2-0001-Record-range-constructor-functions-in-pg_range.patch)
download | inline diff:
From 82a087573dd60765321abb7904de271404e59e6f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 19 Jan 2026 13:54:41 +0100
Subject: [PATCH v2] Record range constructor functions in pg_range
When a range type is created, several construction functions are also
created, two for the range type and three for the multirange type.
These have an internal dependency, so they "belong" to the range type.
But there was no way to identify those functions when given a range
type. An upcoming patch needs access to the two- or possibly the
three-argument range constructor function for a given range type. The
only way to do that would be with fragile workarounds like matching
names and argument types. The correct way to do that kind of thing is
to record to the links in the system catalogs. This is what this
patch does, it records the OIDs of these five constructor functions in
the pg_range catalog. (Currently, there is no code that makes use of
this.)
Reviewed-by: Paul A Jungwirth <[email protected]>
Discussion: https://www.postgresql.org/message-id/7d63ddfa-c735-4dfe-8c7a-4f1e2a621058%40eisentraut.org
TODO: catversion
---
doc/src/sgml/catalogs.sgml | 54 +++++++++++++++++++++
src/backend/catalog/pg_range.c | 9 +++-
src/backend/commands/typecmds.c | 48 +++++++++++++-----
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 | 59 ++++++++++++++++++++++-
src/test/regress/sql/type_sanity.sql | 47 +++++++++++++++++-
8 files changed, 230 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2fc63442980..332193565e2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6676,6 +6676,60 @@ <title><structname>pg_range</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngconstruct2</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 2-argument range constructor function (lower and upper)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngconstruct3</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 3-argument range constructor function (lower, upper, and
+ flags)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct0</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 0-argument multirange constructor function (constructs empty
+ range)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct1</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 1-argument multirange constructor function (constructs
+ multirange from single range, also used as cast function)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>rngmltconstruct2</structfield> <type>regproc</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the 2-argument multirange constructor function (constructs
+ multirange from array of ranges)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>rngcanonical</structfield> <type>regproc</type>
diff --git a/src/backend/catalog/pg_range.c b/src/backend/catalog/pg_range.c
index cd21c84c8fd..cb8c79d0e83 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 rangeConstruct2, RegProcedure rangeConstruct3,
+ RegProcedure mltrngConstruct0, RegProcedure mltrngConstruct1, RegProcedure mltrngConstruct2)
{
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_rngconstruct2 - 1] = ObjectIdGetDatum(rangeConstruct2);
+ values[Anum_pg_range_rngconstruct3 - 1] = ObjectIdGetDatum(rangeConstruct3);
+ values[Anum_pg_range_rngmltconstruct0 - 1] = ObjectIdGetDatum(mltrngConstruct0);
+ values[Anum_pg_range_rngmltconstruct1 - 1] = ObjectIdGetDatum(mltrngConstruct1);
+ values[Anum_pg_range_rngmltconstruct2 - 1] = ObjectIdGetDatum(mltrngConstruct2);
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..288edb25f2f 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 *rangeConstruct2_p, Oid *rangeConstruct3_p);
static void makeMultirangeConstructors(const char *name, Oid namespace,
Oid multirangeOid, Oid rangeOid,
- Oid rangeArrayOid, Oid *castFuncOid);
+ Oid rangeArrayOid,
+ Oid *mltrngConstruct0_p, Oid *mltrngConstruct1_p, Oid *mltrngConstruct2_p);
static Oid findTypeInputFunction(List *procname, Oid typeOid);
static Oid findTypeOutputFunction(List *procname, Oid typeOid);
static Oid findTypeReceiveFunction(List *procname, Oid typeOid);
@@ -1406,6 +1408,11 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
ListCell *lc;
ObjectAddress address;
ObjectAddress mltrngaddress PG_USED_FOR_ASSERTS_ONLY;
+ Oid rangeConstruct2Oid = InvalidOid;
+ Oid rangeConstruct3Oid = InvalidOid;
+ Oid mltrngConstruct0Oid = InvalidOid;
+ Oid mltrngConstruct1Oid = InvalidOid;
+ Oid mltrngConstruct2Oid = InvalidOid;
Oid castFuncOid;
/* Convert list of names to a name and namespace */
@@ -1661,10 +1668,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 +1749,18 @@ 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,
+ &rangeConstruct2Oid, &rangeConstruct3Oid);
makeMultirangeConstructors(multirangeTypeName, typeNamespace,
multirangeOid, typoid, rangeArrayOid,
- &castFuncOid);
+ &mltrngConstruct0Oid, &mltrngConstruct1Oid, &mltrngConstruct2Oid);
+ castFuncOid = mltrngConstruct1Oid;
+
+ /* Create the entry in pg_range */
+ RangeCreate(typoid, rangeSubtype, rangeCollation, rangeSubOpclass,
+ rangeCanonical, rangeSubtypeDiff, multirangeOid,
+ rangeConstruct2Oid, rangeConstruct3Oid,
+ mltrngConstruct0Oid, mltrngConstruct1Oid, mltrngConstruct2Oid);
/* Create cast from the range type to its multirange type */
CastCreate(typoid, multirangeOid, castFuncOid, InvalidOid, InvalidOid,
@@ -1769,10 +1780,14 @@ DefineRange(ParseState *pstate, CreateRangeStmt *stmt)
*
* We actually define 2 functions, with 2 through 3 arguments. This is just
* to offer more convenience for the user.
+ *
+ * The OIDs of the created functions are returned through the pointer
+ * arguments.
*/
static void
makeRangeConstructors(const char *name, Oid namespace,
- Oid rangeOid, Oid subtype)
+ Oid rangeOid, Oid subtype,
+ Oid *rangeConstruct2_p, Oid *rangeConstruct3_p)
{
static const char *const prosrc[2] = {"range_constructor2",
"range_constructor3"};
@@ -1833,6 +1848,11 @@ makeRangeConstructors(const char *name, Oid namespace,
* pg_dump depends on this choice to avoid dumping the constructors.
*/
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+
+ if (pronargs[i] == 2)
+ *rangeConstruct2_p = myself.objectId;
+ else if (pronargs[i] == 3)
+ *rangeConstruct3_p = myself.objectId;
}
}
@@ -1842,13 +1862,13 @@ makeRangeConstructors(const char *name, Oid namespace,
* If we had an anyrangearray polymorphic type we could use it here,
* but since each type has its own constructor name there's no need.
*
- * Sets castFuncOid to the oid of the new constructor that can be used
- * to cast from a range to a multirange.
+ * The OIDs of the created functions are returned through the pointer
+ * arguments.
*/
static void
makeMultirangeConstructors(const char *name, Oid namespace,
Oid multirangeOid, Oid rangeOid, Oid rangeArrayOid,
- Oid *castFuncOid)
+ Oid *mltrngConstruct0_p, Oid *mltrngConstruct1_p, Oid *mltrngConstruct2_p)
{
ObjectAddress myself,
referenced;
@@ -1899,6 +1919,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
* depends on this choice to avoid dumping the constructors.
*/
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct0_p = myself.objectId;
pfree(argtypes);
/*
@@ -1939,8 +1960,8 @@ makeMultirangeConstructors(const char *name, Oid namespace,
0.0); /* prorows */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct1_p = myself.objectId;
pfree(argtypes);
- *castFuncOid = myself.objectId;
/* n-arg constructor - vararg */
argtypes = buildoidvector(&rangeArrayOid, 1);
@@ -1978,6 +1999,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
0.0); /* prorows */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+ *mltrngConstruct2_p = 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..fa5e6ff0c3e 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',
+ rngconstruct2 => 'int4range(int4,int4)', rngconstruct3 => 'int4range(int4,int4,text)',
+ rngmltconstruct0 => 'int4multirange()', rngmltconstruct1 => 'int4multirange(int4range)', rngmltconstruct2 => 'int4multirange(_int4range)',
rngcanonical => 'int4range_canonical', rngsubdiff => 'int4range_subdiff' },
{ rngtypid => 'numrange', rngsubtype => 'numeric',
rngmultitypid => 'nummultirange', rngsubopc => 'btree/numeric_ops',
+ rngconstruct2 => 'numrange(numeric,numeric)', rngconstruct3 => 'numrange(numeric,numeric,text)',
+ rngmltconstruct0 => 'nummultirange()', rngmltconstruct1 => 'nummultirange(numrange)', rngmltconstruct2 => 'nummultirange(_numrange)',
rngcanonical => '-', rngsubdiff => 'numrange_subdiff' },
{ rngtypid => 'tsrange', rngsubtype => 'timestamp',
rngmultitypid => 'tsmultirange', rngsubopc => 'btree/timestamp_ops',
+ rngconstruct2 => 'tsrange(timestamp,timestamp)', rngconstruct3 => 'tsrange(timestamp,timestamp,text)',
+ rngmltconstruct0 => 'tsmultirange()', rngmltconstruct1 => 'tsmultirange(tsrange)', rngmltconstruct2 => 'tsmultirange(_tsrange)',
rngcanonical => '-', rngsubdiff => 'tsrange_subdiff' },
{ rngtypid => 'tstzrange', rngsubtype => 'timestamptz',
rngmultitypid => 'tstzmultirange', rngsubopc => 'btree/timestamptz_ops',
+ rngconstruct2 => 'tstzrange(timestamptz,timestamptz)', rngconstruct3 => 'tstzrange(timestamptz,timestamptz,text)',
+ rngmltconstruct0 => 'tstzmultirange()', rngmltconstruct1 => 'tstzmultirange(tstzrange)', rngmltconstruct2 => 'tstzmultirange(_tstzrange)',
rngcanonical => '-', rngsubdiff => 'tstzrange_subdiff' },
{ rngtypid => 'daterange', rngsubtype => 'date',
rngmultitypid => 'datemultirange', rngsubopc => 'btree/date_ops',
+ rngconstruct2 => 'daterange(date,date)', rngconstruct3 => 'daterange(date,date,text)',
+ rngmltconstruct0 => 'datemultirange()', rngmltconstruct1 => 'datemultirange(daterange)', rngmltconstruct2 => 'datemultirange(_daterange)',
rngcanonical => 'daterange_canonical', rngsubdiff => 'daterange_subdiff' },
{ rngtypid => 'int8range', rngsubtype => 'int8',
rngmultitypid => 'int8multirange', rngsubopc => 'btree/int8_ops',
+ rngconstruct2 => 'int8range(int8,int8)', rngconstruct3 => 'int8range(int8,int8,text)',
+ rngmltconstruct0 => 'int8multirange()', rngmltconstruct1 => 'int8multirange(int8range)', rngmltconstruct2 => '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..32ee8cf43a0 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 rngconstruct2 BKI_LOOKUP(pg_proc);
+ regproc rngconstruct3 BKI_LOOKUP(pg_proc);
+
+ /* multirange constructor functions */
+ regproc rngmltconstruct0 BKI_LOOKUP(pg_proc);
+ regproc rngmltconstruct1 BKI_LOOKUP(pg_proc);
+ regproc rngmltconstruct2 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 rangeConstruct2, RegProcedure rangeConstruct3,
+ RegProcedure mltrngConstruct0, RegProcedure mltrngConstruct1, RegProcedure mltrngConstruct2);
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..25aaae8d05a 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 {rngconstruct2} => pg_proc {oid}
+NOTICE: checking pg_range {rngconstruct3} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct0} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct1} => pg_proc {oid}
+NOTICE: checking pg_range {rngmltconstruct2} => 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..1d21d3eb446 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.rngconstruct2 = 0 OR r.rngconstruct3 = 0
+ OR r.rngmltconstruct0 = 0 OR r.rngmltconstruct1 = 0 OR r.rngmltconstruct2 = 0;
rngtypid | rngsubtype
----------+------------
(0 rows)
@@ -663,6 +665,61 @@ WHERE r.rngmultitypid IS NULL OR r.rngmultitypid = 0;
----------+------------+---------------
(0 rows)
+-- check constructor function arguments and return types
+--
+-- proname and prosrc are not required to have these particular
+-- values, but this matches 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.rngconstruct2 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.rngconstruct3 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.rngmltconstruct0 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.rngmltconstruct1 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.rngmltconstruct2 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..95d5b6e0915 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.rngconstruct2 = 0 OR r.rngconstruct3 = 0
+ OR r.rngmltconstruct0 = 0 OR r.rngmltconstruct1 = 0 OR r.rngmltconstruct2 = 0;
-- rngcollation should be specified iff subtype is collatable
@@ -491,6 +493,49 @@ 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 to have these particular
+-- values, but this matches 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.rngconstruct2 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.rngconstruct3 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.rngmltconstruct0 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.rngmltconstruct1 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.rngmltconstruct2 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
base-commit: 34740b90bc123d645a3a71231b765b778bdcf049
--
2.52.0
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 Application Time Update & Delete
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox