MIME-Version: 1.0
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.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> <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
 <e59d218a-ae49-490f-ae6f-86c12f35d3c3@eisentraut.org>
In-Reply-To: <e59d218a-ae49-490f-ae6f-86c12f35d3c3@eisentraut.org>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Mon, 19 Jan 2026 10:33:02 -0800
Message-ID: <CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@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%2BrenyWKOj5%3DrMmQmJcbybu-Vdomxdp%3DeJ93kp76AgmQKYdfiQ%40mail.gmail.com>
Precedence: bulk

On Mon, Jan 19, 2026 at 5:37=E2=80=AFAM Peter Eisentraut <peter@eisentraut.=
org> wrote:
>
> 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.

I agree that seems like an improvement.

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

Okay.

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

Okay. And ABI compatibility is only between minor versions, so no concern t=
here.

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

Okay, that's great!

Do we want a regress test in rangetypes.sql to confirm that these are
set correctly (especially for user-defined types)? I checked manually
after `make installcheck`, and they look fine, but should it be in our
test suite?

Here is another thought I had: As we've talked about in the
application-time threads, I would like temporal features to be
extensible enough to support user-defined types. We almost achieve
that, but we need something like a "type support function". For primary
key and unique constraints, we need a way to reject invalid values like
empty ranges. For foreign keys we need an intersect operator (which is
not currently in pg_amop, since it is neither for search nor ordering,
and isn't involved in indexes anyway). And for UPDATE/DELETE FOR
PORTION OF we need a foo_minus_multi to compute the "temporal
leftovers".

We could also ask for a constructor function, to build the targeted
portion from the FROM/TO bounds. This is not strictly necessary, since
we also have the FOR PORTION OF valid_at (...) syntax (which is used by
multiranges). But it's something that would be nice to offer. In that
case range types would not need these extra columns in pg_range.

But recording the constructor oids in pg_range still has inherent
value, and doing it now doesn't *prevent* us from later adding a
facility to get a constructor function for FOR PORTION OF bounds. So I
don't think there is any downside to recording them here.

Yours,

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