public inbox for [email protected]
help / color / mirror / Atom feedFrom: Vik Fearing <[email protected]>
To: Paul Jungwirth <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Corey Huinker <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Sat, 2 Sep 2023 00:41:13 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<[email protected]>
<CA+renyWqpNdsi9OaPmXheeQL72adi_iz36VgVsqyyGpQ20FDAg@mail.gmail.com>
<[email protected]>
<CA+renyX0ipvY6A_jUOHeB1q9mL4bEYfAZ5FBB7G7jUo5bykjrA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+renyVyMOb+t6QNoq=PBK1CY_oa9wdJrObjj6S1-RK9CxYwKQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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
view thread (66+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 application time
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox