Temporal extensions
От | Dave Jones |
---|---|
Тема | Temporal extensions |
Дата | |
Msg-id | 553C361D.3090107@waveform.org.uk обсуждение исходный текст |
Ответы |
Re: Temporal extensions
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
Список | pgsql-hackers |
Hi all, My apologies I couldn't directly respond to the earlier thread on this subject (http://www.postgresql.org/message-id/50D99278.3030704@dc.baikal.ru) but I wasn't subscribed to the list at that point. I've been working on a conversion of several utilities I wrote for another engine, and was wondering if there was any interest in seeing any of them added to contrib/ at some point in the vague undefined future? The github repo for the source is here: https://github.com/waveform80/oliphant The documentation for the extensions (currently) resides here: http://oliphant.readthedocs.org/en/latest/ (Obviously the docs would need conversion to docbook for any official sort of patch; I've used docbook in the past, I mainly wrote the docs in rst because that's what I'm most used to at the moment and it's awfully convenient :) The major extension is "history" which is intended for the tracking of temporal data. This comprises various functions for the creation of history tables, their associated triggers, and utility views for providing alternate transformations of the historical data. The other extensions ("assert", "auth", "merge") all really exist in support of "history" (to some greater or lesser degree) but I'd split them out previously as on the original engine (DB2) they served some purpose and perhaps they can serve some here. However, I'd have no issue removing such dependence and simply merging the relevant code into the "history" extension if that was deemed appropriate (as you can guess, it's "history" I'm really interested in.). The extensions are all written in plpgsql (no C), and there's some rudimentary tests of their functionality under the tests/ dir (which again would need converting/expanding in the event they were to become "official"). That said, having read through the former thread (referenced above) I get the impression there's still plenty I need to think about. I predict some questions are bound to arise, so I'll provide some brief answers introductory below: Q. Why not build on the existing work? Honestly, I didn't think to go looking for it until I considered posting to this list, and it's been enlightening seeing what others have done in this space (doh!). Personally, I started tinkering with this sort of stuff long ago, in a database engine far far away* and the stuff I had, had fulfilled all my needs in this space. So, when it came time to move onto postgres that's where I started (for better or worse). * http://groups.google.com/group/comp.databases.ibm-db2/msg/e84aeb1f6ac87e6c Q. Why are you using two timestamp fields instead of a range? Short answer: performance. I did some tests with a relatively large history data set using the two-field timestamp method, and a range method before starting work on the "history" extension. These tests seemed to indicate that using ranges significantly impacted performance (both writes, and queries). It didn't look terribly difficult to convert the code between the two systems so for the time being I pressed ahead with the two-field method, but I'd love to find out if I was doing something stupid with ranges. Further discussion definitely wanted! Q. Why AFTER triggers instead of BEFORE? Largely because on the original engine BEFORE triggers were limited in functionality, so they had to be AFTER triggers. After reading some of the discussion on the linked thread, this may be a decision I have to re-visit. Q. What about official SQL:2011 syntax? Application time, etc.? Sure - all stuff I'd love to see in Postgres at some point, but not stuff I'm qualified to even begin looking at (given it requires engine alterations). Anyway, that's probably enough for now. Questions, suggestions, criticisms all gratefully received! Dave.
В списке pgsql-hackers по дате отправления: