Re: Proposal: temporal extension "period" data type

On Mon, 2008-05-26 at 06:49 -0400, Andrew Dunstan wrote:
> >  * temporal foreign keys
> >  * temporal joins
> >   
> 
> Maybe I'm being dense, but how does inclusion in core help you do these 
> things? Or, more precisely, how does non-inclusion in core prevent them.

Temporal FK's can be implemented with triggers, but can't be
implemented with the current FK syntax and mechanism. Any extensions to
the FK syntax to support this would need to be done in postgres. 

Temporal joins can be implemented by joining with the "overlaps"
operator and selecting the intersection of the two period columns.
Anything added would be syntactic sugar.

> 
> >  * syntax like "ALTER TABLE ... ADD LOG".
> >   
> 
> What does this mean?
> 

The general idea is the same as the idea behind pgfoundry projects like:

http://pgfoundry.org/projects/tablelog/ and
http://pgfoundry.org/projects/aupg/

The idea is to record all deleted or updated tuples in a table so that
you can see a historical state of the table (i.e. a snapshot query) or
see some changes to the data over time.

Having some kind of syntax to back up a feature like that could be
useful for simplifying the creation of a log. Also, people may be more
comfortable using some built-in syntax if they are creating an audit log
for security purposes.

Regards,Jeff Davis