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.