Re: Temporal features in PostgreSQL - Mailing list pgsql-hackers

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