Message-ID: <1f22c9c2-3879-4ece-8a4e-f69ba6df9df7@illuminatedcomputing.com>
Date: Sat, 11 May 2024 20:25:45 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: jian he <jian.universality@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@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>
 <CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
 <1b2ab118-82c1-4e79-a094-04dab382e2c4@illuminatedcomputing.com>
 <CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
 <3775839b-3f0f-4c8a-ac03-a253222e6a4b@illuminatedcomputing.com>
 <CACJufxFQ4EkJDTZXzgC3boid7dnXaRD8gW1KcXi_H-JAg2FPMA@mail.gmail.com>
 <CACJufxHMbSiKX9BTg4P_vHj3BUf4uGyfprNYPgz_jrQw6RTsxg@mail.gmail.com>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <CACJufxHMbSiKX9BTg4P_vHj3BUf4uGyfprNYPgz_jrQw6RTsxg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/1f22c9c2-3879-4ece-8a4e-f69ba6df9df7%40illuminatedcomputing.com>
Precedence: bulk

On 5/11/24 17:00, jian he wrote:
>> I hope I understand the problem correctly.
>> my understanding is that we are trying to solve a corner case:
>> create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS));
>> insert into t values ('[1,2]','empty'), ('[1,2]','empty');
>>
> 
> 
> but we still not yet address for cases like:
> create table t10(a int4range, b int4range, unique (a, b WITHOUT OVERLAPS));
> insert into t10 values ('[1,2]','empty'), ('[1,2]','empty');
> 
> one table can have more than one temporal unique constraint,
> for each temporal unique constraint adding a check isempty constraint
> seems not easy.

I think we should add the not-empty constraint only for PRIMARY KEYs, not all UNIQUE constraints. 
The empty edge case is very similar to the NULL edge case, and while every PK column must be 
non-null, we do allow nulls in ordinary UNIQUE constraints. If users want to have 'empty' in those 
constraints, I think we should let them. And then the problems you give don't arise.

> Maybe we can just mention that the special 'empty' range value makes
> temporal unique constraints not "unique".

Just documenting the behavior is also an okay solution here I think. I see two downsides though: (1) 
it makes rangetype temporal keys differ from PERIOD temporal keys (2) it could allow more 
planner/etc bugs than we have thought of. So I think it's worth adding the constraint instead.

> also we can make sure that
> FOREIGN KEY can only reference primary keys, not unique temporal constraints.
> so the unique temporal constraints not "unique" implication is limited.
> I played around with it, we can error out these cases in the function
> transformFkeyCheckAttrs.

I don't think it is a problem to reference a temporal UNIQUE constraint, even if it contains empty 
values. An empty value means you're not asserting that row at any time (though another row might 
assert the same thing for some time), so it could never contribute toward fulfilling a reference anyway.

I do think it would be nice if the *reference* could contain empty values. Right now the FK SQL will 
cause that to never match, because we use `&&` as an optimization, but we could tweak the SQL (maybe 
for v18 instead) so that users could get away with that kind of thing. As I said in an earlier 
email, this would be you an escape hatch to reference a temporal table from a non-temporal table. 
Otherwise temporal tables are "contagious," which is a bit of a drawback.

Yours,

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