Re: Periods

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.