MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CACJufxFFeUHoBomd2856amDBccNVQZzA=Jrk1_FDdvSZ1+X7og@mail.gmail.com>
 <CACJufxEACgLY7YUvzo8j=iOB-z6-1QHAvMr8cUHRP7b_F_vo3A@mail.gmail.com>
 <CA+renyXHFFyq1n2+4cwcFoLXqzUhb6u6WCL6wjk8ZjP9WxTExw@mail.gmail.com>
 <f22f65c8-865d-4753-94cc-3b954c29ad5c@eisentraut.org> <feee527a-6c47-42b9-a947-2422adb45a1c@illuminatedcomputing.com>
 <CACJufxF8HyERm7SKBJ+duxAP9sTgVhaW6b-oWADANGgn-o4wPA@mail.gmail.com>
 <b4bc1ef8-4215-4cf6-933c-5ef2a2827efe@illuminatedcomputing.com>
 <85dd3ad0-fa79-46e8-a08b-c426c76825f3@eisentraut.org> <a5d8df78-9ff6-4273-8d06-6c85b5520520@illuminatedcomputing.com>
 <49c07eac-18ec-4aff-929c-e81b87df1092@eisentraut.org> <CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
 <d579a67c-e8d7-4eb7-ae14-f6fca01489cf@illuminatedcomputing.com>
 <1edb3b79-a4bc-43b1-80aa-2e090472fe32@eisentraut.org> <47550967-260b-4180-9791-b224859fe63e@illuminatedcomputing.com>
 <64c2b2ab-7ce9-475e-ac59-3bfec528bada@eisentraut.org> <58b6687fe9de002e4c862a412719e4f918bad987.camel@j-davis.com>
 <CA+Tgmob518sSV4Y8F4LN3X-7+7DmExznG1wCbYrOurfPL_xBOg@mail.gmail.com>
In-Reply-To: <CA+Tgmob518sSV4Y8F4LN3X-7+7DmExznG1wCbYrOurfPL_xBOg@mail.gmail.com>
From: Isaac Morland <isaac.morland@gmail.com>
Date: Tue, 21 May 2024 14:27:29 -0400
Message-ID: <CAMsGm5duDGbSp4un6JaRn_MvujfTWCNMPSFDzJNnyW9jn-dWcA@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>, Peter Eisentraut <peter@eisentraut.org>, 
	Paul Jungwirth <pj@illuminatedcomputing.com>, jian he <jian.universality@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003107ff0618faf924"
Archived-At: <https://www.postgresql.org/message-id/CAMsGm5duDGbSp4un6JaRn_MvujfTWCNMPSFDzJNnyW9jn-dWcA%40mail.gmail.com>
Precedence: bulk

--0000000000003107ff0618faf924
Content-Type: text/plain; charset="UTF-8"

On Tue, 21 May 2024 at 13:57, Robert Haas <robertmhaas@gmail.com> wrote:

What I think is less clear is what that means for temporal primary
> keys. As Paul pointed out upthread, in every other case, a temporal
> primary key is at least as unique as a regular primary key, but in
> this case, it isn't. And someone might reasonably think that a
> temporal primary key should exclude empty ranges just as all primary
> keys exclude nulls. Or they might think the opposite.
>

Fascinating. I think you're absolutely right that it's clear that two empty
intervals don't conflict. If somebody wants to claim two intervals
conflict, they need to point to at least one instant in time that is common
between them.

But a major point of a primary key, it seems to me, is that it uniquely
identifies a row. If items are identified by a time range, non-overlapping
or not, then the empty range can only identify one item (per value of
whatever other columns are in the primary key). I think for a unique key
the non-overlapping restriction has to be considered an additional
restriction on top of the usual uniqueness restriction.

I suspect in many applications there will be a non-empty constraint; for
example, it seems quite reasonable to me for a meeting booking system to
forbid empty meetings. But when they are allowed they should behave in the
mathematically appropriate way.

--0000000000003107ff0618faf924
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, 21 May 2024 at 13:57, Robert Haas=
 &lt;<a href=3D"mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>&gt;=
 wrote:<br></div><div class=3D"gmail_quote"><div><br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;b=
order-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"=
>
What I think is less clear is what that means for temporal primary<br>
keys. As Paul pointed out upthread, in every other case, a temporal<br>
primary key is at least as unique as a regular primary key, but in<br>
this case, it isn&#39;t. And someone might reasonably think that a<br>
temporal primary key should exclude empty ranges just as all primary<br>
keys exclude nulls. Or they might think the opposite.=C2=A0<br></blockquote=
><div><br></div><div>Fascinating. I think you&#39;re absolutely right that =
it&#39;s clear that two empty intervals don&#39;t conflict. If somebody wan=
ts to claim two intervals conflict, they need to point to at least one inst=
ant in time that is common between them.</div><div><br></div><div>But a maj=
or point of a primary key, it seems to me, is that it uniquely identifies a=
 row. If items are identified by a time range, non-overlapping or not, then=
 the empty range can only identify=C2=A0one item (per value of whatever oth=
er columns are in the primary key). I think for a unique key the non-overla=
pping restriction has to be considered an additional restriction on top of =
the usual uniqueness restriction.</div><div><br></div><div>I suspect in man=
y applications there will be a non-empty constraint; for example, it seems =
quite reasonable to me for a meeting booking system to forbid empty meeting=
s. But when they are allowed they should behave in the mathematically appro=
priate way.</div></div></div>

--0000000000003107ff0618faf924--