Re: temporal support patch - Mailing list pgsql-hackers

On Wed, 2012-05-16 at 23:14 +0200, Miroslav Šimulčík wrote:
> Hi all,
> 
> 
> as a part of my master's thesis I have created temporal support patch
> for PostgreSQL. It enables the creation of special temporal tables
> with entries versioning. Modifying operations (UPDATE, DELETE,
> TRUNCATE) on these tables don't cause permanent changes to entries,
> but create new versions of them. Thus user can easily get to the past
> states of the table.
> 
I would be very interested to see this, thank you for working on it.

There are quite a few aspects to a temporal database system, and you are
working on a system-maintained transaction-time historical table, right?
Or are there other aspects to your proposal?

Some general comments:

* I'd very much like to see you make use of Range Types from 9.2; in
particular, TSTZRANGE would be much better than holding two timestamps.
If a standard requires you to display two timestamps in certain
situations, perhaps you could use ranges internally and display the
boundaries as timestamps when needed.

* There is other useful information that could be recorded, such as the
user who inserted/updated/deleted the record.

* For some purposes, it's very useful to keep track of the columns that
changed. For instance, a query like "show me any time a salary was
changed over the last month" (or some other rare event) would be very
slow to run if there was not some explicit annotation on the historical
records (e.g. a "columns changed" bitmap or something).

* In general, I'm not fond of adorning queries with TRANSACTION TIME AS
OF... kinds of things. Those constructs are redundant with a WHERE
clause (on a range type, you'd use the "contains" operator). If a
standard requires that, maybe it would be OK to allow such things as
syntactic sugar.

* I do like having special DDL that creates the appropriate objects.
That helps to guide users so they don't have to invent their own
solution with triggers, etc.

* As Jim mentioned, it might make sense to use something resembling
inheritance so that selecting from the historical table includes the
current data (but with no upper bound for the range).

* It might make sense to hammer out as many of the details as we can
with an extension. For instance, exactly what options will be available,
what data types will be used, what objects will be created, the trigger
code, etc. Then, it will be more obvious exactly what we need to add
extra core support for (e.g. if we are going to use some inheritance
like mechanism), and what we need to add syntax sugar for.

I recommend that you start posting more detailed designs on
http://wiki.postgresql.org

If you already have code, feel free to submit it for the next commitfest
( http://commitfest.postgresql.org ), but this is a relatively large
project, so it will most likely take several commitfest cycles.

Regards,Jeff Davis