Re: Temporal features in PostgreSQL

On 02/04/2013 07:40 PM, Miroslav Šimulčík wrote:
> 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(at)dc(dot)baikal(dot)ru
> <mailto: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-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(at)postgresql(dot)org
> <mailto:pgsql-hackers(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>