Re: Temporal features in PostgreSQL

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.
- I don't need to deal with update conflicts, because I use
clock_timestamp() instead of current_timestamp.
- Inheritence relation between historical and current table allows to
easily select whole history of rows.

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

Regards,
Miro

2012/12/25 Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)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-05/msg00765.php>
> http://archives.postgresql.**org/pgsql-hackers/2012-08/**msg00680.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<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/<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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-hackers<http://www.postgresql.org/mailpref/pgsql-hackers>
>