4 ms·

> I'd love to see someone translate the concepts in both books to a useful postgres package. This is some work on this. [1] is an amazing patch by some researc

> I'd love to see someone translate the concepts in both books to a useful postgres package. This is some work on this. [1] is an amazing patch by some researchers in Europe that has already had several rounds of review. Their first published work was against Postgres 9.1! Their solution is way better than SQL:2011, but could be used behind-the-scenes to implement the features from the standard. Essentially they implement a temporal variant for every operator in the relational algebra (very elegantly btw, by combining standard operators with just two new functions). That approach solves some of the problems that led to Snodgrass's original temporal proposal getting rejected in the late 90s (and which are still holding back SQL:2011 IMO). Whereas the standard only gives temporal INNER JOIN, their work gives every join type, as well as aggregates (including a way to scale the inputs if the group only includes part of their time interval). It also lets queries compose better through subqueries, views, CTEs, set-returning functions, etc. In my opinion their work would make Postgres the most advanced temporal database in the world. I hope it gets the attention it needs! I also have a very modest WIP patch myself at [2], which adds temporal-aware primary and foreign keys. This is all based on the excellent work on ranges and exclusion constraints by Jeff Davis and others. Postgres has an excellent foundation, but needs the higher-level concepts so that users can more easily set up temporal tables and use them to answer queries and record changes. If you'd like to learn more about temporal databases, I wrote a summary of the research at [3]. I'm also giving a talk at the Postgres conference in Ottawa next month.... :-) EDIT: Btw, if all you need is transaction time (usually included for auditing and compliance), then the temporal_tables extension [4] should be all you need. Maybe also consider the pgaudit extension too [5]. [1] https://commitfest.postgresql.org/22/2045/ https://commitfest.postgresql.org/22/2045/ [2] https://commitfest.postgresql.org/22/2048/ https://commitfest.postgresql.org/22/2048/ [3] https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/ https://illuminatedcomputing.com/posts/2017/12/temporal-data... [4] https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables [5] https://github.com/pgaudit/pgaudit https://github.com/pgaudit/pgaudit

vjt 8y ago
I wrote a Ruby library[1] that builds on Active Record and implements SCD-Type 2 using updatable views and INSTEAD OF triggers. It has its pain points, mostly related to how hackish the AR insertion is, but it works quite well and the approach ensures that any INSERT/UPDATE/DELETE triggers bookkeeping of the history tables. [1] https://github.com/ifad/chronomodel https://github.com/ifad/chronomodel
- pjungwir 8y ago
  Oh I really admire Chronomodel! Actually I link to it in my annotated bibliography ([3] above). Thanks for all your work on that! (Also: I exchanged some emails with one of the Chronomodel devs a few months ago. Perhaps that was you?) For valid-time DML, I think the most useful operation is an upsert/merge. Tom Johnston calls this an `INSERT WHENEVER` I think. I have a not-yet-published Rails project that also uses INSTEAD OF triggers to convert AR saves to valid-time upserts. Hopefully I can get that published soon. I would love to even contribute it to Chronomodel eventually if that makes sense, so that it can be bitemporal. Although there is tons of research in temporal relational database design, the rest of the stack really needs attention. What does temporal mean for REST and CRUD? Or your ORM? What are good UX patterns for viewing and editing a valid-time history? Can you make save-as-you-type work or do you need a Save button? I'd love to see people start working on that! My own Rails experiment landed on one way, and for me it validated that the db-level features would actually be useful, but there are other approaches possible.
michelpp 8y ago
Wow great summary, thank you!

vjt 8y ago

I wrote a Ruby library[1] that builds on Active Record and implements SCD-Type 2 using updatable views and INSTEAD OF triggers. It has its pain points, mostly related to how hackish the AR insertion is, but it works quite well and the approach ensures that any INSERT/UPDATE/DELETE triggers bookkeeping of the history tables. [1] https://github.com/ifad/chronomodel https://github.com/ifad/chronomodel

pjungwir 8y ago
Oh I really admire Chronomodel! Actually I link to it in my annotated bibliography ([3] above). Thanks for all your work on that! (Also: I exchanged some emails with one of the Chronomodel devs a few months ago. Perhaps that was you?) For valid-time DML, I think the most useful operation is an upsert/merge. Tom Johnston calls this an `INSERT WHENEVER` I think. I have a not-yet-published Rails project that also uses INSTEAD OF triggers to convert AR saves to valid-time upserts. Hopefully I can get that published soon. I would love to even contribute it to Chronomodel eventually if that makes sense, so that it can be bitemporal. Although there is tons of research in temporal relational database design, the rest of the stack really needs attention. What does temporal mean for REST and CRUD? Or your ORM? What are good UX patterns for viewing and editing a valid-time history? Can you make save-as-you-type work or do you need a Save button? I'd love to see people start working on that! My own Rails experiment landed on one way, and for me it validated that the db-level features would actually be useful, but there are other approaches possible.

michelpp 8y ago

Wow great summary, thank you!