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 <arhi...@dc.baikal.ru
<mailto:arhi...@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
<mailto:pgsql-hackers@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
