Re: SQL:2011 application time

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(at)illuminatedcomputing(dot)com