Temporal extensions

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.