On June 5, 2018 9:47 PM, Paul A Jungwirth p...@illuminatedcomputing.com wrote:
> On Sat, May 26, 2018 at 1:56 PM, Vik Fearing vik.fear...@protonmail.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.
