Re: Periods - Mailing list pgsql-hackers
From | Vik Fearing |
---|---|
Subject | Re: Periods |
Date | |
Msg-id | gTH9M3J2oHguJIOkJ8uxNlc9NgMpDlkbG-alEwfAlG6WDsMTFAS6S0NRJUb2mGOE1Yxag7RiteytaADq117PbV6asnvWip2Zmz-3w0MiXWE=@protonmail.com Whole thread Raw |
In response to | Re: Periods (Paul A Jungwirth <pj@illuminatedcomputing.com>) |
Responses |
Re: Periods
|
List | pgsql-hackers |
On Sat, May 26, 2018 at 1:56 PM, Vik Fearing vik.fearing@protonmail.com wrote:SQL:2011 introduced the concept of a "period". It takes two existing columnsand basically does the same thing as our range types except there is no newstorage. I believe if Jeff Davis had given us range types a few years laterthan he did, it would have been using periods.Hi Vik, I'm really glad to see someone working on temporal features!I've only dabbled in Postgres hacking, but I've been followingtemporal database research for several years, so I hope you won't mindmy comments. I already shared some thoughts on this other thread:
Hi! No, of course I don't mind your comments; I welcome them. I had not seen that thread so I'll go take a look at it.
I would love to see Postgres support the standard but also letpeople use range types. I'm not sure I agree that Jeff Davis wouldhave preferred the SQL:2011 period idea, which is an extra-relationalconcept. Since it is attached to a table, it doesn't carry throughcleanly to a result set, so what happens if you want to apply temporalfeatures to a view, subquery, CTE, or set-returning function?
As far as I can see, the standard doesn't say what should happen if you select a period, or even if that's possible. It does however define how to create a period not attached to a table (PERIOD <left paren> <period start value> <comma> <period end value> <right paren>) so it would be possible to use that for views, subqueries, and the rest of your examples.
A range on the other hand is just a type, so as long as temporal operatorssupport that type, everything still composes nicely and just works.The Date/Darwen/Lorenztos book has more to say about that, and I thinkit's worth reading. They are unrealistically extreme in their purism,but here I think they have some good points---points they also raisedagainst an earlier proposed temporal-SQL standard circa 1998. By theway here are some thoughts Jeff shared with me about that book, whichhe says inspired range types:
Thanks, I will read this, too.
I understand that your patch is just to allow defining periods, but Ithought I'd share my own hopes earlier rather than later, in case youare doing more work on this.
Yes, I plan on doing much more work on this. My goal is to implement (by myself or with help from other) the entire SQL:2016 spec on periods and system versioned tables. This current patch is just infrastructure.
Also, it might be nice if Postgres letyou also define periods from a single range column, so that people whowant to use intervals can still stick closer to the standard---Idunno, just an idea.
That's a nice idea, but I'm not sure how I'd fit it into the pg_period catalog which expects two columns.
Also, this may not be very helpful, but I started an extension tosupport temporal foreign keys here:It uses intervals, not periods, but maybe you can steal some ideas.:-) I have a half-finished branch porting it from plpgsql to C, sothat I could give them more catalog integration, and also I have hopesof defining temporal primary keys, although that isn't implementedyet. Anyway, I mention it because you said, "Application periods canbe used in PRIMARY/UNIQUE keys, foreign keys," but feel free to ignoreit. :-)
While I'm waiting for comments on how best to do inheritance and other aspects of my patch, I'm working on getting PRIMARY/UNIQUE keys with periods. That's far from finished though as it is touching parts of the code that I have never looked at before.
In general, I would love Postgres to have some lower-level primitiveslike range types and the Dingös operators, and then build theSQL:2011 support on top of those. I'm happy to contribute work to helpmake that happen, although I'd probably need to work with someone withmore Postgres hacking experience to get it done.
Any help you can give me (or that I could give you) is greatly appreciated. I'm hoping we can get *something* in v12 with periods.
pgsql-hackers by date: