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 по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: Variable-length FunctionCallInfoData
Следующее
От: Paul A Jungwirth
Дата:
Сообщение: Re: Periods