Re: Temporal features in PostgreSQL - Mailing list pgsql-hackers
| From | Miroslav Šimulčík |
|---|---|
| Subject | Re: Temporal features in PostgreSQL |
| Date | |
| Msg-id | CAHRNM68XhtikLn3xC-fwtqv4OCQKJ4LP1xLNr4z9BRnwZqVe1A@mail.gmail.com Whole thread Raw |
| In response to | Temporal features in PostgreSQL (Vlad Arkhipov <arhipov@dc.baikal.ru>) |
| Responses |
Re: Temporal features in PostgreSQL
(Vlad Arkhipov <arhipov@dc.baikal.ru>)
|
| 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.
- 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@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
pgsql-hackers by date: