Re: SQL:2011 application time

On 9/1/23 21:56, Paul Jungwirth wrote:
> On 9/1/23 03:50, Vik Fearing wrote:
>> On 9/1/23 11:30, Peter Eisentraut wrote:
>>> 1) If I write UNIQUE (a, b, c WITHOUT OVERLAPS), does the WITHOUT 
>>> OVERLAPS clause attach to the last column, or to the whole column 
>>> list? In the SQL standard, you can only have one period and it has to 
>>> be listed last, so this question does not arise.  But here we are 
>>> building a more general facility to then build the SQL facility on 
>>> top of.  So I think it doesn't make sense that the range column must 
>>> be last or that there can only be one.  Also, your implementation 
>>> requires at least one non-overlaps column, which also seems like a 
>>> confusing restriction.
>>>
>>> I think the WITHOUT OVERLAPS clause should be per-column, so that 
>>> something like UNIQUE (a WITHOUT OVERLAPS, b, c WITHOUT OVERLAPS) 
>>> would be possible.  Then the WITHOUT OVERLAPS clause would directly 
>>> correspond to the choice between equality or overlaps operator per 
>>> column.
>>>
>>> An alternative interpretation would be that WITHOUT OVERLAPS applies 
>>> to the whole column list, and we would take it to mean, for any range 
>>> column, use the overlaps operator, for any non-range column, use the 
>>> equals operator.  But I think this would be confusing and would 
>>> prevent the case of using the equality operator for some ranges and 
>>> the overlaps operator for some other ranges in the same key.
>>
>> I prefer the first option.  That is: WITHOUT OVERLAPS applies only to 
>> the column or expression it is attached to, and need not be last in line.
> 
> I agree. The second option seems confusing and is more restrictive.
> 
> I think allowing multiple uses of `WITHOUT OVERLAPS` (and in any 
> position) is a great recommendation that enables a lot of new 
> functionality. Several books[1,2] about temporal databases describe a 
> multi-dimensional temporal space (even beyond application time vs. 
> system time), and the standard is pretty disappointing here. It's not a 
> weird idea.
> 
> But I just want to be explicit that this isn't something the standard 
> describes. (I think everyone in the conversation so far understands 
> that.) So far I've tried to be pretty scrupulous about following 
> SQL:2011, although personally I'd rather see Postgres support this 
> functionality. And it's not like it goes *against* what the standard 
> says. But if there are any objections, I'd love to hear them before 
> putting in the work. :-)


I have no problem with a first version doing exactly what the standard 
says and expanding it later.


> If we allow multiple+anywhere WITHOUT OVERLAPS in PRIMARY KEY & UNIQUE 
> constraints, then surely we also allow multiple+anywhere PERIOD in 
> FOREIGN KEY constraints too. (I guess the standard switched keywords 
> because a FK is more like "MUST OVERLAPS". :-)


Seems reasonable.


> Also if you have multiple application-time dimensions we probably need 
> to allow multiple FOR PORTION OF clauses. I think the syntax would be:
> 
> UPDATE t
>    FOR PORTION OF valid_at FROM ... TO ...
>    FOR PORTION OF asserted_at FROM ... TO ...
>    [...]
>    SET foo = bar
> 
> Does that sound okay?


That sounds really cool.


> [1] C. J. Date, Hugh Darwen, Nikos Lorentzos. Time and Relational 
> Theory, Second Edition: Temporal Databases in the Relational Model and 
> SQL. 2nd edition, 2014.
> [2] Tom Johnston. Bitemporal Data: Theory and Practice. 2014.


Thanks!  I have ordered these books.
-- 
Vik Fearing