Re: PG and Temporal - Mailing list pgsql-sql

On Mon, Jun 01, 2015 at 02:02:34PM -0400, Ravi Krishna wrote:
> On Mon, Jun 1, 2015 at 1:25 PM, Steve Midgley <science@misuse.org> wrote:
> >
> > I think there are a lot of theories as to how to make temporal table systems
> > work. It hugely depends on your requirements. That said, the data warehouse
> > community has built a kind of solution with dimension tables representing
> > time, and fact tables aligning to those dimensions via relations. It makes
> > certain temporal "grain size" problems much easier to solve (and usually
> > faster). Though using Pg's date extraction functions I've gotten pretty
> > amazing performance as well: basically creating on-demand time dimensions as
> > needed.. Read Ralph Kimball's work on data warehousing for a good
> > introduction.
> >
> > This isn't the same as creating versioned tables per your references above
> > but I hope will be useful in your research..
> 
> The bi-temporal I am talking about has nothing to do with
> datawarehouse. It basically needs two things to be satisfied
> 
> 1. What was my table like as on a particular date. Like SELECT * FROM
> TABLE  WHERE system_time  =
> 
> This is called system time.
> 
> 2. What is the effective date of a row. Imagine interest rate which
> has a start  date  and  an end date. This is  called business time. By
> implementing business time in the database, lot  of checks  can be
> pushed  to  the server (like constraint checking).
> 
> Currently  only DB2 offers full implementation (SQL 2011)  of both (1) and (2).
> 
> The add-on I  downloaded offers  (1) only  and  that too partial, as
> there is no sql  support for querying a table  as of a system time.

Hi Ravi,

I ran into this issue as well, and ended up using a combination of tables
tagged with a tstzrange column (I only needed a unitemporal model). The
tstzrange column has a default value of [NOW(),'infinity), and then I have
a suite of stored procedures called by triggers for the various state
transitions DML will take the tables (and referenced tables) through. An
EXCLUDE constraint using GiST indices over the tstzrange and the business
key columns is the equivalent of a UNIQUE on a non-temporal table.

To handle UPDATE/DELETE DML, I have two stored procedures, one for each
operation. The UPDATE procedure is called as a BEFORE UPDATE TRIGGER, while
the DELETE procedure is AFTER DELETE DEFERRABLE INITIALLY DEFERRED. UPDATEs
get mapped to an UPDATE+INSERT:

1. UPDATE the existing row's tstzrange column so the upper value is NOW()
(rather than infinity). This takes the existing row out of effective time.
2. INSERT the new row.

DELETE is just step 1 of the UPDATE above.

The biggest messiness I ran into was maintaining referential integrity, as
you can't just use standard REFERENCES DDL. I ended up writing five stored
procedures, which depend on data from the system catalog and consistent
column naming to operate. The INSERT/UPDATE checks are BEFORE triggers,
while DELETE checks are AFTER DEFERRABLE INITIALLY DEFERRED. For
many-to-many relationships, I use a small table that contains both table
names, along with the foreign key columns on each side.

To make all of this behave like a standard, non-temporal database, I
defined views with the same name as the underlying temporal tables, but in
a separate "present" schema. Each view returns all non-temporal columns,
where tstzrange overlaps with the present (i.e. tstzrange @> NOW()).
Starting with PostgreSQL 9.3, we have updateable views, so end users can
just interact with these as normal.

-- 
-- Skylar Thompson (skylar2@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine