Re: Periods
От | Paul A Jungwirth |
---|---|
Тема | Re: Periods |
Дата | |
Msg-id | CA+renyWCFTV4XFNWSuooed=ugqBUEV0EmH4w1qSnHkhhhHW7LA@mail.gmail.com обсуждение исходный текст |
Ответ на | Periods (Vik Fearing <vik.fearing@protonmail.com>) |
Ответы |
Re: Periods
(Paul A Jungwirth <pj@illuminatedcomputing.com>)
Re: Periods (Vik Fearing <vik.fearing@protonmail.com>) |
Список | 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 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 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? 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 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. 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. 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. :-) 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. Yours, Paul
В списке pgsql-hackers по дате отправления: