Re: SQL:2011 Application Time Update & Delete - Mailing list pgsql-hackers

On Thu, Jan 8, 2026 at 8:03 AM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> 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.

I like this idea!

Patch applies, tests pass.

We would need to document these columns.

> 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?

I don't foresee using any of the others. I'm inclined to record all of
them though, in case someone else has a use for them.

And actually I wonder if UPDATE/DELETE FOR PORTION OF should use the
3-arg constructor. We want to guarantee the FROM is inclusive and the
TO is exclusive. That's true for built-in rangetypes, but we should be
explicit to ensure we get the right behavior for other rangetypes too.

```
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);

```

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`.

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?

```
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);
```

This all looks good to me.

```
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' },

 ]
```

Do the .dat files have a way to set oidvector columns?

```
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.

```
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
...
```

I like the tests you've added here.

This needs some kind of pg_upgrade support I assume? It will have to
work for user-defined rangetypes too. So I guess we would still need
some code like what's in my patch, although keeping it just for the
v18 -> v19 upgrade seems better than having it in core indefinitely.

Yours,

--
Paul              ~{:-)
pj@illuminatedcomputing.com