Re: Temporal features in PostgreSQL

From: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
To: cedric(at)2ndquadrant(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org, Miroslav Šimulčík <simulcik(dot)miro(at)gmail(dot)com>
Subject: Re: Temporal features in PostgreSQL
Date: 2013-02-18 02:12:42
Message-ID: 51218E1A.5030409@dc.baikal.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On 02/15/2013 10:46 PM, Cédric Villemain wrote:
>
> Hello,
>
> I'm also interested in this topic.
>
> > > I'm also interested in this topic and work on system-time temporal
>
> > > extension. Here I wrote down design of my solution few months ago
>
> > > https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is
>
> > > basically the same as in your solution with some minor differences.
>
> I've added a requirement in the system here: the table to be versioned
>
> must have a PK (I dislike _entry_id usage but this sounds good othwise).
>
> I then define a "EXCLUDE WITH GIST (pk with =, sys_period with &&)", thus
>
> getting expected UNIQUEness also in the history.
>
I use similar constraints for application-time period tables but not for
system versioned. Because they are automatically controlled by a
trigger, there should be no need for additional integrity checks. If you
want to speed up queries against historical data, you can create GIST
index or an exclusion constraint.

> Vlad, is your source code in a public versionning system (github,
> bucket, etc) ?
>
> It will ease the process to participate to your extension...
>

Yes, I uploaded it on github
https://github.com/arkhipov/temporal_tables/

The extension is also available on PGXN
http://pgxn.org/dist/temporal_tables/1.0.0/

> --
>
> Cédric Villemain +33 (0)6 20 30 22 52
>
> http://2ndQuadrant.fr/
>
> PostgreSQL: Support 24x7 - Développement, Expertise et Formation
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2013-02-18 04:07:12 Re: [PATCH] Add PQconninfoParseParams and PQconninfodefaultsMerge to libpq
Previous Message Tomas Vondra 2013-02-17 23:49:17 Re: PATCH: Split stats file per database WAS: autovacuum stress-testing our system