Re: Periods
On Thu, Jul 4, 2019 at 11:44 AM Alvaro Herrera wrote: > I think that the functionality in your patch is already integrated in > Paul's patch for temporal PK/FK elsewhere ... is that correct, or is > this patch orthogonal to that work? Hi Vik, I wasn't aware that you had moved your work over to an extension. It looks like you've made a lot of progress! I'd be eager to work with you on getting this into core. Alvaro: it's a mix of orthogonal and not orthogonal. :-) My work lets you use range types directly in SQL:2011 constructs, whereas Vik's work lets you use SQL:2011 PERIODs. I would like to support *both*, so I've been intending to add Vik's original patch letting you define PERIODs to my own work. There is no conflict in supporting both ranges and PERIODs because you can't name a PERIOD the same as an existing column. Behind the scenes the easiest way to implement PERIODs is with range types, so there would be very little duplication to permit both. PERIODs suffer from being outside relational theory as a quasi-attribute. You especially see this in composing queries & result sets. For example with ranges you could say this: WITH x AS ( SELECT * FROM y FOR PORTION OF some_range FROM t1 TO t2 ) SELECT * FROM x FOR PORTION OF some_range FROM t3 TO t4 ; But with PERIODs you can't because a PERIOD is not included in `SELECT *`. Also it's unclear how "attached" it is to a table definition. Can you say `SELECT *, some_period FROM x`? If so can you then use `FOR PORTION OF` on that result set? Can you construct an on-the-fly PERIOD expression? Can you pass a period to a function as a parameter? Can a function return a period? Can you ORDER BY a period? GROUP BY one? Can you cast to/from a period? Can you ask a period for its high/low values? Do we treat a PERIOD as a whole new datatype? Can you define a real column of type PERIOD? I haven't found text from the standard that answers most of these questions. (The standard does say you can construct a `PERIOD(t1, t2)` expression but apparently only inside a "period predicate".) Also you can't define PERIODs on types other than date/timestamp/timestamptz, unlike ranges. Also PERIODs require a sentinel value to mean "unbounded" (e.g. 31-JAN-) whereas ranges let you express that with a NULL. (Postgres does have Infinity and -Infinity for timestamp types but I've noticed that client programming languages can't always express ranges with those values.) Personally I intend to use ranges any time I build temporal tables, but supporting PERIODs might have value for people more interested in database portability or someone migrating from elsewhere to Postgres. I had some conversations at PGCon that I felt validated the permit-PERIODS-or-ranges approach, so I'm about ready to expand my patch to handle PERIODs too. For that I would love to draw on Vik's work so far. I think his original patch against core is more likely to be helpful than the extension, but I'll certainly consult both, and Vik if you have any advice let me know! :-) A big difference between a temporal extension vs temporal features in core is implementing DML. An extension pretty much requires you to use INSTEAD OF triggers. Also Vik's README points out that implementing temporal DELETE is hard that way. In core I believe you'd do temporal DML in the executor node. (That's my working theory anyway; I'm still new to that part of the code.) The first thing on my TODO list is to write a blog post comparing how other RDMBSes handle PERIODs and other temporal features. Besides the questions above, how does a trigger work on a table? For example when you DELETE in the middle of a range/period, and it becomes an INSERT plus an UPDATE, I *believe* you still fire the DELETE trigger. And you need to set the NEW/OLD tuples appropriately. You *don't* fire any INSERT & UPDATE triggers. The standard isn't super explicit but that's my take on it, and I want to write down what other vendors are doing. Yours, Paul
Re: Periods
Hello Vik, Paul, On 2019-Jul-04, Vik Fearing wrote: > I've been working on this as an extension instead. It allows me to do some > stuff in plpgsql which is much easier for me. > > https://github.com/xocolatl/periods/ Hmm, okay. > I would love to have all this in core (especially since MariaDB 10.4 > just became the first open source database to get all of this > functionality), but something is better than nothing. Agreed on all accounts. I think that the functionality in your patch is already integrated in Paul's patch for temporal PK/FK elsewhere ... is that correct, or is this patch orthogonal to that work? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Periods
On Thursday, July 4, 2019 8:04 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: > Hello Vik, > On 2018-Jun-05, Vik Fearing wrote: > >>> 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. > > Have you had the chance to work on this? Hi Alvaro, I've been working on this as an extension instead. It allows me to do some stuff in plpgsql which is much easier for me. https://github.com/xocolatl/periods/ I would love to have all this in core (especially since MariaDB 10.4 just became the first open source database to get all of this functionality), but something is better than nothing. -- Vik Fearing
Re: Periods
Hello Vik, On 2018-Jun-05, Vik Fearing wrote: > > 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. Have you had the chance to work on this? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Periods
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 ) 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.
Re: Periods
On Tue, Jun 5, 2018 at 12:47 PM, Paul A Jungwirth wrote: > 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. Sorry for two emails but I wanted to add: the more stealable thing are the tests, which are pretty thorough and took a lot of hours to write. They are yours if you want them. :-) Paul
Re: Periods
On Sat, May 26, 2018 at 1:56 PM, Vik Fearing 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
Re: Periods
2018-05-26 22:56 GMT+02:00 Vik Fearing : > 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. > > Attached is a WIP patch that I have been working on. The only thing left > is completing periods on inherited tables, and finishing up pg_dump. I'm > posting this now just to make sure my basic foundation is sound, and to let > people know that I'm working on this. > > The patch itself doesn't have any functionality, it just allows periods to > be defined. With that, there are several things that we can do: > SYSTEM_TIME periods, which are explicitly not allowed by this patch, will > allow us to do SQL standard versioned tables, and also allows some time > travel functionality. Application periods can be used in PRIMARY/UNIQUE > keys, foreign keys, and give nice new features to UPDATE and DELETE. They > also allow "period predicates" which are the same kind of operations we > already have for range types. All of that is for future patches that build > on the infrastructure presented in this patch. > > The SQL standard restricts period columns to dates or timestamps, but I'm > allowing anything that has a btree operator class, as is the PostgreSQL > way. System periods, once allowed, will only be timestamptz though. > Unfortunately, I had to fully reserve the word PERIOD for this. > > I'm looking for comments on everything except the pg_dump stuff, keeping > in mind that inheritance is not finished either. > > Thanks! > looks interesting Regards Pavel > > > This is patch is based off of 71b349aef4. >
