Re: Periods

On June 5, 2018 9:47 PM, Paul A Jungwirth pj(at)illuminatedcomputing(dot)com wrote:

> On Sat, May 26, 2018 at 1:56 PM, Vik Fearing vik(dot)fearing(at)protonmail(dot)com wrote:
>
>> SQL:2011 introduced the concept of a "period". It takes two existing columns
>> and basically does the same thing as our range types except there is no new
>> storage. I believe if Jeff Davis had given us range types a few years later
>> than 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 following
> temporal database research for several years, so I hope you won't mind
> my comments. I already shared some thoughts on this other thread:
> http://www.postgresql-archive.org/SQL-2011-Valid-Time-Support-td6020221.html

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 let
> people use range types. I'm not sure I agree that Jeff Davis would
> have preferred the SQL:2011 period idea, which is an extra-relational
> concept. Since it is attached to a table, it doesn't carry through
> cleanly to a result set, so what happens if you want to apply temporal
> features 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 operators
> support that type, everything still composes nicely and just works.
> The Date/Darwen/Lorenztos book has more to say about that, and I think
> it's worth reading. They are unrealistically extreme in their purism,
> but here I think they have some good points---points they also raised
> against an earlier proposed temporal-SQL standard circa 1998. By the
> way here are some thoughts Jeff shared with me about that book, which
> he says inspired range types:
> https://news.ycombinator.com/item?id=14738655

Thanks, I will read this, too.

> I understand that your patch is just to allow defining periods, but I
> thought I'd share my own hopes earlier rather than later, in case you
> are 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 let
> you also define periods from a single range column, so that people who
> want to use intervals can still stick closer to the standard---I
> dunno, 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 to
> support temporal foreign keys here:
> https://github.com/pjungwir/time_for_keys
> It uses intervals, not periods, but maybe you can steal some ideas.
> :-) I have a half-finished branch porting it from plpgsql to C, so
> that I could give them more catalog integration, and also I have hopes
> of defining temporal primary keys, although that isn't implemented
> yet. Anyway, I mention it because you said, "Application periods can
> be used in PRIMARY/UNIQUE keys, foreign keys," but feel free to ignore
> it. :-)

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 primitives
> like range types and the Dingös operators, and then build the
> SQL:2011 support on top of those. I'm happy to contribute work to help
> make that happen, although I'd probably need to work with someone with
> more 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.