Re: Temporal features in PostgreSQL

p, li { white-space: pre-wrap; }

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