Re: Temporal features in PostgreSQL

<div class="moz-cite-prefix">Hi,<br /><br /> On 02/15/2013 10:46 PM, Cédric Villemain wrote:<br /></div><blockquote
cite="mid:201302151446.56725.cedric@2ndquadrant.com"type="cite"><style type="text/css"> 
p, li { white-space: pre-wrap; }
</style><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0;
text-indent:0px;      -qt-user-state:0;">Hello,<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px;       -qt-block-indent:0; text-indent:0px; "> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">I'malso interested in this topic.<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px;       -qt-block-indent:0; text-indent:0px; "> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> I'm also interested in this topic and work on system-time temporal <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> extension. Here I wrote down design of my solution few months ago <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> <a class="moz-txt-link-freetext"
href="https://wiki.postgresql.org/wiki/SQL2011Temporal">https://wiki.postgresql.org/wiki/SQL2011Temporal</a>.The idea
is<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0;
text-indent:0px;      -qt-user-state:0;">> > basically the same as in your solution with some minor differences.
<pstyle="-qt-paragraph-type:empty; margin-top:0px;       margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px;       -qt-user-state:0;">I've added a requirement in the system
here:the table to be versioned <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px;
-qt-block-indent:0;text-indent:0px;       -qt-user-state:0;">must have a PK (I dislike _entry_id usage but this sounds
goodothwise).<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0;
text-indent:0px;      -qt-user-state:0;">I then define a "EXCLUDE WITH GIST (pk with =, sys_period with &&)",
thus<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0;
text-indent:0px;      -qt-user-state:0;">getting expected UNIQUEness also in the history.</blockquote> I use similar
constraintsfor application-time period tables but not for system versioned. Because they are automatically controlled
bya trigger, there should be no need for additional integrity checks. If you want to speed up queries against
historicaldata, you can create GIST index or an exclusion constraint.<br /><br /><blockquote
cite="mid:201302151446.56725.cedric@2ndquadrant.com"type="cite">  <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;      margin-right:0px; -qt-block-indent:0; text-indent:0px;       -qt-user-state:0;">Vlad, is your
sourcecode in a public versionning system (github, bucket, etc) ?<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;      margin-right:0px; -qt-block-indent:0; text-indent:0px;       -qt-user-state:0;">It will ease the
processto participate to your extension...</blockquote><br /> Yes, I uploaded it on github<br /><a
class="moz-txt-link-freetext"
href="https://github.com/arkhipov/temporal_tables/">https://github.com/arkhipov/temporal_tables/</a><br/><br /> The
extensionis also available on PGXN<br /><a class="moz-txt-link-freetext"
href="http://pgxn.org/dist/temporal_tables/1.0.0/">http://pgxn.org/dist/temporal_tables/1.0.0/</a><br/><br
/><blockquotecite="mid:201302151446.56725.cedric@2ndquadrant.com" type="cite"><p style="-qt-paragraph-type:empty;
margin-top:0px;      margin-bottom:0px; margin-left:0px; margin-right:0px;       -qt-block-indent:0; text-indent:0px;
"> <pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0;
text-indent:0px;      -qt-user-state:0;">-- <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px;       -qt-user-state:0;">Cédric Villemain +33 (0)6 20 30 22 52<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
     -qt-user-state:0;"><a class="moz-txt-link-freetext" href="http://2ndQuadrant.fr/">http://2ndQuadrant.fr/</a><p
style="margin-top:0px; margin-bottom:0px; margin-left:0px;       margin-right:0px; -qt-block-indent:0; text-indent:0px;
     -qt-user-state:0;">PostgreSQL: Support 24x7 - Développement, Expertise et Formation<p
style="-qt-paragraph-type:empty;margin-top:0px;       margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> </blockquote>