Re: RFC: Temporal Extensions for PostgreSQL

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Warren Turkal <wt(at)penguintechs(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: RFC: Temporal Extensions for PostgreSQL
Date: 2007-02-16 20:13:35
Message-ID: 20070216201335.GS19527@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

My suggestion would be to focus on a period data type first and
foremost, as that's something that could be readily used by a lot of
folks. Of particular note, it's difficult to query tables that have
start_time and end_time fields to define a period; it's easy to screw up
the boundary conditions, and it's also hard to make those queries
perform well without going to extra lengths (such as defining a 'bogus'
GiST index on something like box(point(start,start),point(end,end)). And
it's not possible to do that in a way that avoids floating points and
their errors.

On Sat, Feb 10, 2007 at 12:20:28AM -0700, Warren Turkal wrote:
> 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
>

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2007-02-16 20:39:24 Re: RFC: Temporal Extensions for PostgreSQL
Previous Message Gregory Stark 2007-02-16 19:41:59 Short varlena header bit-packing options