Re: SQL:2011 PERIODS vs Postgres Ranges?

Hi

ne 21. 10. 2018 v 21:47 odesílatel Paul A Jungwirth <
pj(at)illuminatedcomputing(dot)com> napsal:

> On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
> wrote:
> > On 21/10/2018 21:17, Paul A Jungwirth wrote:
> > > 3. Build our own abstractions on top of ranges, and then use those to
> > > implement PERIOD-based features.
> > +1 on this approach. I think [7] got the model right. If we can
> > implement SQL-standard PERIODs on top of it, then that's a bonus, but
> > having sane, flexible, coherent set of range operators is more important
> > to me.
>
> Okay, I'm surprised to hear from you and Isaac that following the
> standard isn't as important as I thought, but I'm certainly pleased
> not to make it the focus. I just thought that Postgres's reputation
> was to be pretty careful about sticking to it. (I think we could still
> add a standard-compliant layer, but like you I don't feel a duty to
> suffer from it.) It sounds like I should work out some proposed
> function signatures and write up how to use them, and see what people
> think. Is that a useful approach?
>
>
It can be very unhappy if we cannot to implement standard syntax and
behave. The implementation behind or another is not too important. We
should not to accept any design that don't allow implement standard.

The world is 10 years after standards (maybe more). Now, this feature is
implemented in MySQL/MariaDB, and I expecting a press to have standardized
syntax after 5 years.

Regards

Pavel

> > What are we missing?
>
> Here are a few big ones:
>
> 1. Define temporal primary keys and foreign keys that are known to the
> database catalog and controlled as higher-level objects. For instance
> I wrote an extension at https://github.com/pjungwir/time_for_keys to
> create temporal foreign keys, but the database isn't "aware" of them.
> That means they are more cluttered in `\d foo` than necessary (you see
> the trigger constraints instead of something about a foreign key),
> they don't automatically disappear if you drop the column, it is hard
> to make them "polymorphic" (My extension supports only
> int+tstzrange.), they don't validate that the referenced table has a
> declared temporal PK, they probably have slightly different
> locking/transaction semantics than the real RI code, etc. This is what
> I'd like to implement right now.
>
> 2. System time: automatically track DML changes to the table, and let
> you query "as of" a given time.
>
> 3. Temporal joins. I don't want to tackle this myself, because there
> is already an amazing proposed patch that does everything we could ask
> for at
> https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html
> (recently updated btw, so I hope someone will look at it!).
>
> 4. Temporal UPDATE/DELETE: these should be converted to instead change
> the end time of old rows and insert new rows with the changed
> attributes. I'm interested in implementing this too, but one thing at
> a time. . . .
>
> I really appreciate your sharing your thoughts!
>
> Paul
>
>