Re: SQL:2011 PERIODS vs Postgres Ranges?
От | Paul A Jungwirth |
---|---|
Тема | Re: SQL:2011 PERIODS vs Postgres Ranges? |
Дата | |
Msg-id | CA+renyWg4U2chBSCXrYYMZ=aDUgiUYEr1JfvU-jR6rgAYWaF5g@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: SQL:2011 PERIODS vs Postgres Ranges? (Heikki Linnakangas <hlinnaka@iki.fi>) |
Ответы |
Re: SQL:2011 PERIODS vs Postgres Ranges?
(Pavel Stehule <pavel.stehule@gmail.com>)
|
Список | pgsql-hackers |
On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote: > On 21/10/2018 21:17, Paul A Jungwirth wrote: > > 3. Build our own abstractions on top of ranges, and then use those to > > implement PERIOD-based features. > +1 on this approach. I think [7] got the model right. If we can > implement SQL-standard PERIODs on top of it, then that's a bonus, but > having sane, flexible, coherent set of range operators is more important > to me. Okay, I'm surprised to hear from you and Isaac that following the standard isn't as important as I thought, but I'm certainly pleased not to make it the focus. I just thought that Postgres's reputation was to be pretty careful about sticking to it. (I think we could still add a standard-compliant layer, but like you I don't feel a duty to suffer from it.) It sounds like I should work out some proposed function signatures and write up how to use them, and see what people think. Is that a useful approach? > What are we missing? Here are a few big ones: 1. Define temporal primary keys and foreign keys that are known to the database catalog and controlled as higher-level objects. For instance I wrote an extension at https://github.com/pjungwir/time_for_keys to create temporal foreign keys, but the database isn't "aware" of them. That means they are more cluttered in `\d foo` than necessary (you see the trigger constraints instead of something about a foreign key), they don't automatically disappear if you drop the column, it is hard to make them "polymorphic" (My extension supports only int+tstzrange.), they don't validate that the referenced table has a declared temporal PK, they probably have slightly different locking/transaction semantics than the real RI code, etc. This is what I'd like to implement right now. 2. System time: automatically track DML changes to the table, and let you query "as of" a given time. 3. Temporal joins. I don't want to tackle this myself, because there is already an amazing proposed patch that does everything we could ask for at https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html (recently updated btw, so I hope someone will look at it!). 4. Temporal UPDATE/DELETE: these should be converted to instead change the end time of old rows and insert new rows with the changed attributes. I'm interested in implementing this too, but one thing at a time. . . . I really appreciate your sharing your thoughts! Paul
В списке pgsql-hackers по дате отправления: