RFC: Temporal Extensions for PostgreSQL - Mailing list pgsql-hackers

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 productsWHERE 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)