Thread: Temporal features in PostgreSQL

Hi all,

Currently I'm working on a large enterprise project that heavily uses 
temporal features. We are using PostgreSQL database for data storage. 
Now we are using PL/pgSQL trigger-based and application-based solutions 
to handle with temporal data. However we would like to see this 
functionality in PostgreSQL core, especially in SQL 2011 syntax. There 
were some discussions several months ago on temporal support and audit logs:

http://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php
http://archives.postgresql.org/pgsql-hackers/2012-08/msg00680.php

But currently it seems that there is no active work in this area (am I 
wrong?) Now I'm rewriting our temporal solutions into an extension that 
is based on C-language triggers to get a better sense of the problem 
space and various use cases. There are two aspects that temporal 
features usually include: system-time (aka transaction-time) and 
application-time (aka valid-time or business-time). The topics above 
discussed only the first one. However there is also another one, which 
includes application-time periods, partial updated/deletes queries, 
querying for a portion of application time etc. Details can be found here


http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf

or in the SQL-2011 Standard Draft which is available freely on the 
network. It's hard to create a convenient extension for application-time 
periods because it needs the parser to be changed (however an extension 
may be useful for referential integrity checks for application-time 
period temporal tables).

I created a simple solution for system-time period temporal tables, that 
consist of only one trigger (it resembles SPI/timetravel trigger but is 
based on new range types that were introduced in PostgreSQL 9.2 and it's 
closer to the SQL-2011 approach for implementation of temporal features).

http://pgxn.org/dist/temporal_tables/1.0.0/

I'm not a PostgreSQL expert, so I would appreciate if someone could 
review the code briefly. There are some places I'm not sure I use some 
functions properly. Also there are some slight problems with the design 
that I would like to discuss if anyone is interested in.

Hi Vlad,

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. For example:

     - I use after triggers to store old versions of rows into historical table, so the row is "archived" only if modification is actualy executed.

Then other BEFORE triggers are not able to see what time is going to be inserted into the table. I considered using two triggers, BEFORE trigger for setting the period and AFTER trigger for archiving rows into the history table, but did not find any use cases when it can be better than just a simple BEFORE trigger.

     - I don't need to deal with update conflicts, because I use clock_timestamp() instead of current_timestamp.

You can still come across a conflict even with clock_timestamp(). What if clocks go back during the time synchronization? Even if you have absolutely precious clocks, there are may be clock skew on different CPUs, low system clock time resolution, etc.

   

Although my solution needs changes in parser to stick with SQL 2011 standard, maybe you can find something that can help you.

I believe that SQL-2011 standard temporal features are not too abstract for PostgreSQL to be implemented as a core feature. They have only two temporal periods: application period (which is controlled by application/user) and system time (which is controlled by system/database, but you cannot specify *how* the system control it), they does not use a special type for storing periods (which is unefficient), they are tied to DATE/TIMESTAMP types (what if you need to store revision numbers instead of time?)

Regards,

Miro

2012/12/25 Vlad Arkhipov <arhipov@dc.baikal.ru>

Hi all,

Currently I'm working on a large enterprise project that heavily uses temporal features. We are using PostgreSQL database for data storage. Now we are using PL/pgSQL trigger-based and application-based solutions to handle with temporal data. However we would like to see this functionality in PostgreSQL core, especially in SQL 2011 syntax. There were some discussions several months ago on temporal support and audit logs:

http://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php
http://archives.postgresql.org/pgsql-hackers/2012-08/msg00680.php

But currently it seems that there is no active work in this area (am I wrong?) Now I'm rewriting our temporal solutions into an extension that is based on C-language triggers to get a better sense of the problem space and various use cases. There are two aspects that temporal features usually include: system-time (aka transaction-time) and application-time (aka valid-time or business-time). The topics above discussed only the first one. However there is also another one, which includes application-time periods, partial updated/deletes queries, querying for a portion of application time etc. Details can be found here

http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf

or in the SQL-2011 Standard Draft which is available freely on the network. It's hard to create a convenient extension for application-time periods because it needs the parser to be changed (however an extension may be useful for referential integrity checks for application-time period temporal tables).

I created a simple solution for system-time period temporal tables, that consist of only one trigger (it resembles SPI/timetravel trigger but is based on new range types that were introduced in PostgreSQL 9.2 and it's closer to the SQL-2011 approach for implementation of temporal features).

http://pgxn.org/dist/temporal_tables/1.0.0/

I'm not a PostgreSQL expert, so I would appreciate if someone could review the code briefly. There are some places I'm not sure I use some functions properly. Also there are some slight problems with the design that I would like to discuss if anyone is interested in.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

<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,<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'm also 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-timetemporal <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
monthsago <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;">> > https://wiki.postgresql.org/wiki/SQL2011Temporal. 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. <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'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 good othwise).<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;">Ithen 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;">gettingexpected UNIQUEness also in the history.<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;">Vlad, is
yoursource code 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 process to
participateto your extension...<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;">-- <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;">http://2ndQuadrant.fr/<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; ">  

<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> 

On 2/13/13 10:06 PM, Vlad Arkhipov wrote:
>>      - I don't need to deal with update conflicts, because I use clock_timestamp() instead of current_timestamp.
> You can still come across a conflict even with clock_timestamp(). What if clocks go back during the time
synchronization?Even if you have absolutely precious clocks, there are may be clock skew on different CPUs, low system
clocktime resolution, etc.
 

Sorry for the late reply, catching up on email...

If you want to track the history of something, "measured time" is absolutely NOT the way to do it. I use the term
"measuredtime" to differentiate from the real-world concept of time that is forever flowing forward from one instant to
thenext. The problem with measured time is that it's incredibly easy to screw up. Clocks out of sync, clocks running
backwards,etc. Heck, it's not even clear what time you should actually use: transaction start, wallclock, or
transactionend.
 

For any kind of history tracking to actually be robust you have no choice but to link one history record to another so
thatyou can actually walk down a chain. Of course you might want to capture a timestamp as part of your history
metadata,but you better be ready to deal with a history record with a timestamp that is *earlier* than the prior
historyrecord.
 

BTW, we've been working on a generic history implementation at work; hopefully we'll be able to release it relatively
soon.

What's the current status of this topic?
Has someone worked on temporal tables for PostgreSQL since 2012 ?

I'm giving a presentation on Fosdem later this month in Brussels, on the
topic of temporal tables, and would like to give all possibly relevant
information to the audience!

--      Peter Vanroose,        Leuven, Belgium.



--
View this message in context: http://postgresql.nabble.com/Temporal-features-in-PostgreSQL-tp5737881p5834312.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Fri, Jan 16, 2015 at 09:58:22AM -0700, peter@vanroose.be wrote:
> What's the current status of this topic?
> Has someone worked on temporal tables for PostgreSQL since 2012 ?
> 
> I'm giving a presentation on Fosdem later this month in Brussels, on the
> topic of temporal tables, and would like to give all possibly relevant
> information to the audience!

I have not heard of anyone working on this.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +