MIME-Version: 1.0
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.com>
 <CA+renyWcNBdnaW4zc9S03aN+fEbVVB1S+q8e9MEjhM2YB+kkiw@mail.gmail.com>
 <CA+renyUiEu2yTHk+Vy-Rt2xA5Vnp2N_ohN=npiNmMKTh53qDfA@mail.gmail.com>
 <2f5364f3-a1d3-4410-98f3-d788b11e6525@eisentraut.org> <CA+renyW7ZB_k9AgmSFJU2EegL9r1k1sgWo4-9tGGkgwxNqe6kw@mail.gmail.com>
 <CA+renyUodzxAvMnpa_LTvo+Ru1ZKH+Su8KaPvD4iMtguFKzq4g@mail.gmail.com>
 <1ace7bc1-9dd4-42c9-a473-517cef37cce9@eisentraut.org> <CA+renyU-iz_zvM0gGP=dvBPVrz=Jj3qdCjtAh5nLZRhb49xMFw@mail.gmail.com>
 <6F8D7105-BD1C-432D-84F3-BC688C0C8EDC@gmail.com> <9B820A52-D2F6-465D-B258-6FE8EBA59FAE@gmail.com>
 <CA+renyXchHgpMbYX-cR8fuNnnpf_+FJ6PkHoXoa2AgzRnz4vxQ@mail.gmail.com>
 <53a13f97-340f-4d04-9dcc-77ca3ffb6a6a@eisentraut.org> <CA+renyXH3AF6JVzZGVcT5mAo=0QncB-MpWJeqb2JG66sgyq09g@mail.gmail.com>
 <85ac7f0e-d95f-4377-ade0-8941fd328012@eisentraut.org> <CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
 <7d63ddfa-c735-4dfe-8c7a-4f1e2a621058@eisentraut.org>
In-Reply-To: <7d63ddfa-c735-4dfe-8c7a-4f1e2a621058@eisentraut.org>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Fri, 9 Jan 2026 22:16:26 -0800
Message-ID: <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Chao Li <li.evan.chao@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BrenyVXg5pV84wQnGQuK8-%3DqoKw3BiBgQzesxM_LkcxxWmYjA%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 8, 2026 at 8:03=E2=80=AFAM Peter Eisentraut <peter@eisentraut.o=
rg> 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] =3D ObjectIdGetDatum(rangeCanon=
ical);
     values[Anum_pg_range_rngsubdiff - 1] =3D ObjectIdGetDatum(rangeSubDiff=
);
     values[Anum_pg_range_rngmultitypid - 1] =3D
ObjectIdGetDatum(multirangeTypeOid);
+    values[Anum_pg_range_rngconstr2 - 1] =3D ObjectIdGetDatum(rangeConstr2=
);
+    values[Anum_pg_range_rngconstr3 - 1] =3D ObjectIdGetDatum(rangeConstr3=
);
+    values[Anum_pg_range_rngmconstr0 - 1] =3D
ObjectIdGetDatum(multirangeConstr0);
+    values[Anum_pg_range_rngmconstr1 - 1] =3D
ObjectIdGetDatum(multirangeConstr1);
+    values[Anum_pg_range_rngmconstr2 - 1] =3D
ObjectIdGetDatum(multirangeConstr2);

     tup =3D 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/typecmd=
s.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 =3D InvalidOid;
 Oid            binary_upgrade_next_mrng_array_pg_type_oid =3D 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 *stm=
t)
                    InvalidOid); /* type's collation (ranges never have one=
) */
     Assert(multirangeOid =3D=3D 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 *st=
mt)
     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] =3D {"range_constructor2",
     "range_constructor3"};
@@ -1833,6 +1840,8 @@ makeRangeConstructors(const char *name, Oid namespace=
,
          * pg_dump depends on this choice to avoid dumping the constructor=
s.
          */
         recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+
+        rangeConstrOids[i] =3D 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 rangeArray=
Oid,
-                           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] =3D 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] =3D myself.objectId;
     pfree(argtypes);
     *castFuncOid =3D myself.objectId;

@@ -1978,6 +1989,7 @@ makeMultirangeConstructors(const char *name, Oid
namespace,
                              0.0);    /* prorows */
     /* ditto */
     recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
+    multirangeConstrOids[2] =3D 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_rang=
e.dat
index 830971c4944..f1e46a9d830 100644
--- a/src/include/catalog/pg_range.dat
+++ b/src/include/catalog/pg_range.dat
@@ -14,21 +14,33 @@

 { rngtypid =3D> 'int4range', rngsubtype =3D> 'int4',
   rngmultitypid =3D> 'int4multirange', rngsubopc =3D> 'btree/int4_ops',
+  rngconstr2 =3D> 'int4range(int4,int4)', rngconstr3 =3D>
'int4range(int4,int4,text)',
+  rngmconstr0 =3D> 'int4multirange()', rngmconstr1 =3D>
'int4multirange(int4range)', rngmconstr2 =3D>
'int4multirange(_int4range)',
   rngcanonical =3D> 'int4range_canonical', rngsubdiff =3D> 'int4range_subd=
iff' },
 { rngtypid =3D> 'numrange', rngsubtype =3D> 'numeric',
   rngmultitypid =3D> 'nummultirange', rngsubopc =3D> 'btree/numeric_ops',
+  rngconstr2 =3D> 'numrange(numeric,numeric)', rngconstr3 =3D>
'numrange(numeric,numeric,text)',
+  rngmconstr0 =3D> 'nummultirange()', rngmconstr1 =3D>
'nummultirange(numrange)', rngmconstr2 =3D> 'nummultirange(_numrange)',
   rngcanonical =3D> '-', rngsubdiff =3D> 'numrange_subdiff' },
 { rngtypid =3D> 'tsrange', rngsubtype =3D> 'timestamp',
   rngmultitypid =3D> 'tsmultirange', rngsubopc =3D> 'btree/timestamp_ops',
+  rngconstr2 =3D> 'tsrange(timestamp,timestamp)', rngconstr3 =3D>
'tsrange(timestamp,timestamp,text)',
+  rngmconstr0 =3D> 'tsmultirange()', rngmconstr1 =3D>
'tsmultirange(tsrange)', rngmconstr2 =3D> 'tsmultirange(_tsrange)',
   rngcanonical =3D> '-', rngsubdiff =3D> 'tsrange_subdiff' },
 { rngtypid =3D> 'tstzrange', rngsubtype =3D> 'timestamptz',
   rngmultitypid =3D> 'tstzmultirange', rngsubopc =3D> 'btree/timestamptz_o=
ps',
+  rngconstr2 =3D> 'tstzrange(timestamptz,timestamptz)', rngconstr3 =3D>
'tstzrange(timestamptz,timestamptz,text)',
+  rngmconstr0 =3D> 'tstzmultirange()', rngmconstr1 =3D>
'tstzmultirange(tstzrange)', rngmconstr2 =3D>
'tstzmultirange(_tstzrange)',
   rngcanonical =3D> '-', rngsubdiff =3D> 'tstzrange_subdiff' },
 { rngtypid =3D> 'daterange', rngsubtype =3D> 'date',
   rngmultitypid =3D> 'datemultirange', rngsubopc =3D> 'btree/date_ops',
+  rngconstr2 =3D> 'daterange(date,date)', rngconstr3 =3D>
'daterange(date,date,text)',
+  rngmconstr0 =3D> 'datemultirange()', rngmconstr1 =3D>
'datemultirange(daterange)', rngmconstr2 =3D>
'datemultirange(_daterange)',
   rngcanonical =3D> 'daterange_canonical', rngsubdiff =3D> 'daterange_subd=
iff' },
 { rngtypid =3D> 'int8range', rngsubtype =3D> 'int8',
   rngmultitypid =3D> 'int8multirange', rngsubopc =3D> 'btree/int8_ops',
+  rngconstr2 =3D> 'int8range(int8,int8)', rngconstr3 =3D>
'int8range(int8,int8,text)',
+  rngmconstr0 =3D> 'int8multirange()', rngmconstr1 =3D>
'int8multirange(int8range)', rngmconstr2 =3D>
'int8multirange(_int8range)',
   rngcanonical =3D> 'int8range_canonical', rngsubdiff =3D> 'int8range_subd=
iff' },

 ]
```

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,

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