Re: [HACKERS] Temporal features in PostgreSQL
What's the current status of this topic? Has someone worked on temporal tables for PostgreSQL since 2012 ? I'm giving a presentation on Fosdem later this month in Brussels, on the topic of temporal tables, and would like to give all possibly relevant information to the audience! -- Peter Vanroose, Leuven, Belgium. -- View this message in context: http://postgresql.nabble.com/Temporal-features-in-PostgreSQL-tp5737881p5834312.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporal features in PostgreSQL
On 2/13/13 10:06 PM, Vlad Arkhipov wrote: - 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. Sorry for the late reply, catching up on email... If you want to track the history of something, measured time is absolutely NOT the way to do it. I use the term measured time to differentiate from the real-world concept of time that is forever flowing forward from one instant to the next. The problem with measured time is that it's incredibly easy to screw up. Clocks out of sync, clocks running backwards, etc. Heck, it's not even clear what time you should actually use: transaction start, wallclock, or transaction end. For any kind of history tracking to actually be robust you have no choice but to link one history record to another so that you can actually walk down a chain. Of course you might want to capture a timestamp as part of your history metadata, but you better be ready to deal with a history record with a timestamp that is *earlier* than the prior history record. BTW, we've been working on a generic history implementation at work; hopefully we'll be able to release it relatively soon. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporal features in PostgreSQL
Hi, On 02/15/2013 10:46 PM, Cédric Villemain wrote: Hello, I'm also interested in this topic. 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. I've added a requirement in the system here: the table to be versioned must have a PK (I dislike _entry_id usage but this sounds good othwise). I then define a EXCLUDE WITH GIST (pk with =, sys_period with ), thus getting expected UNIQUEness also in the history. I use similar constraints for application-time period tables but not for system versioned. Because they are automatically controlled by a trigger, there should be no need for additional integrity checks. If you want to speed up queries against historical data, you can create GIST index or an exclusion constraint. Vlad, is your source code in a public versionning system (github, bucket, etc) ? It will ease the process to participate to your extension... Yes, I uploaded it on github https://github.com/arkhipov/temporal_tables/ The extension is also available on PGXN http://pgxn.org/dist/temporal_tables/1.0.0/ -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] Temporal features in PostgreSQL
Hello, I'm also interested in this topic. 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. I've added a requirement in the system here: the table to be versioned must have a PK (I dislike _entry_id usage but this sounds good othwise). I then define a EXCLUDE WITH GIST (pk with =, sys_period with ), thus getting expected UNIQUEness also in the history. Vlad, is your source code in a public versionning system (github, bucket, etc) ? It will ease the process to participate to your extension... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] 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 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
Re: [HACKERS] Temporal features in PostgreSQL
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 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.phphttp://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php http://archives.postgresql.**org/pgsql-hackers/2012-08/**msg00680.phphttp://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.pdfhttp://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/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-hackershttp://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Temporal features in PostgreSQL
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
