Temporal Extensions for PostgreSQL by: Warren Turkal I would like to see a comprehensive solution to time varying tables (or temporal) in PostgreSQL. I specifically want to see suuport for valid-time and transacation-time and bitemporal (valid-time and transaction-time) tables. I will be defering the descriptions of much of the functionality to Dr. Richard T. Snodgrass's _Developing Time-Oriented Database Applications in SQL_ at [1]. The mangled pages 30-31 are at [2].
a) Functionality Dr. Richard T. Snodgrass has worked on defining semantics of temporal very completely in several writings. He was also involved in an unsuccessful effort to standardize temporal extensions to SQL. I believe his book does a good job in presenting the semantics of temporal databases and describing extensions to SQL that make the data much more natural with which to work. b) How current solutions fall flat Current solutions fall flat due to the extreme complexity of implementing valid-time and transaction time semantics on tables by adding columns to track all of the data. Please see chapter 11 of [1] for a more complete description of this complexity. Chapter 12 of [1] goes on to lay out new syntax for SQL that will make dealing with data of this nature much more natural. c) Examples --create normal table CREATE TABLE products ( id SERIAL PRIMARY KEY , description TEXT ); -- Add valid-time support to the table with granularity of timestamp. ALTER TABLE products ADD VALIDTIME PERIOD(TIMESTAMP WITH TIMEZONE); -- Insert row valid from 2006-01-01 to just before 2007-01-01 VALIDTIME PERIOD '[2006-01-01 - 2007-01-01)' INSERT INTO products ( description ) VALUES ( 'red ball' ); -- Insert row valid from 2007-01-01 to just before 2008-01-01 -- Should be smart enough to realize the id=777 does not conflict in this time -- of validity. VALIDTIME PERIOD '[2007-01-01 - 2008-01-01)' INSERT INTO products ( id , description ) VALUES ( 777 , 'blue ball' ); -- Select history of products with id=777 VALIDTIME SELECT * FROM product WHERE id=777; id | description | valid_period ---------------------------------------------- 777| red ball | [2006-01-01 - 2007-01-01) 777| blue ball | [2007-01-01 - 2008-01-01) -- Select current products with id=777 -- The date when query was run was 2007-02-10. SELECT * FROM products WHERE id=777; id | description ------------------ 777| blue ball There are many more details in chapter 12 of [1]. d) New stuff (dependencies, indices, syntax, libraries) One of the base level additions is the PERIOD datatype. I think that implementing temporal support is reliant on developing such a type. The description of this datatype is laid out in chapter 4 of [1]. The SQL syntax is present in chapter 12 of [1]. I see this as the first piece that needs to be implemented in order to take steps toward a DBMS to supports full temporal capabilities. I think that PERIOD can largely reuse the datatime functionality for parsing of literals and for comparisons. The RTREE seems to nicely incorporate needed indexing of the PERIOD type. The syntax of the parser will have to be extended to handle the PERIOD literals and constructor. I believe any additional libraries will be required. There are also extensions to the syntax of table creation, table altering, querying, inserting, and updating on temporal tables. These are all discussed in some detail in chapter 12 of [1]. I don't think that any of these changes will require new libraries. The semantics of temporal tables and querying them could have a dramatic affect on how things like primary keys and unique constraints work. I would like to get some comments about this from the community. e) See Also Addtional resources can be found at Dr. Richard T. Snodgrass's website at [3], including SQL valid-time table support spec at [4] and SQL transaction-time table support spec at [5]. Thoughts? Questions? Comments? [1]http://www.cs.arizona.edu/~rts/tdbbook.pdf [2]http://www.cs.arizona.edu/~rts/pp30-31.pdf [3]http://www.cs.arizone.edu/~rts/ [4]ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/mad146.pdf [5]ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/mad147.pdf Thanks, wt -- Warren Turkal (w00t) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq