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
Snodgrass's _Developing Time-Oriented Database Applications in SQL_ at .
mangled pages 30-31 are at .
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  for a more complete description
this complexity. Chapter 12 of  goes on to lay out new syntax for SQL that
will make dealing with data of this nature much more natural.
--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
( '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
, 'blue ball'
-- Select history of products with 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.
id | description
777| blue ball
There are many more details in chapter 12 of .
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 . The SQL syntax
present in chapter 12 of . 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
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
some detail in chapter 12 of . 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
on how things like primary keys and unique constraints work. I would like to
some comments about this from the community.
e) See Also
Addtional resources can be found at Dr. Richard T. Snodgrass's website at ,
including SQL valid-time table support spec at  and SQL transaction-time
table support spec at .
Thoughts? Questions? Comments?
Warren Turkal (w00t)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?