Thread: Implementation of a updateable, "temporal" view on data

Hi!

OK, I'm new to pgsql but would like to implement a updateable,
"temporal" view. By "temporal" I mean that the table has fields for
start and end time of the row's validity. The view on the table should
only display "current" rows.

An "update" on one of those current rows actually consists of two
actions: a) update the old row, setting its "stop" field to now(). b)
insert a new row with updated values, "start"-ing now().

I tried to implement this using pgsql rules on the view - but I seem
unable to restrict step a to only THE old row:
UPDATE table SET stop=now() WHERE table.id=old.id AND table.start=old.start
is "translated" to:
... WHERE table.id=table.id AND table.start=table.start
;(

OK, I fallback to instead-triggers; but triggers on views are not
supported. As applications also need the "archive" access to current and
past row versions, I cannot "trigger" the main table.

Well, second fallback. I try a (second) dummy table with triggers that
update the main table. For SELECT access I try to create a rule -
redirecting the query to current rows of the main table; However, select
rules are only allowed for "true" views...

As such, I'm stuck (with version 8.2.4, if necessary). Could anyone give
me a clue if/how it's possible to implement such a "temporal" view on
data in pgsql?

Any help or link greatly appreciated
HPO

--- Hans-Peter Oeri <hp@oeri.ch> wrote:
> I tried to implement this using pgsql rules on the view - but I seem
> unable to restrict step a to only THE old row:
> UPDATE table SET stop=now() WHERE table.id=old.id AND table.start=old.start
> is "translated" to:
> ... WHERE table.id=table.id AND table.start=table.start
> ;(
>


I would do this a little differently.

INSERT INTO table ( starttime, endtime, val1, val(...) )
VALUES( old.endtime, now(), old.val1, old.val(...) );

UPDATE table
SET starttime = now(),
    val1 = new.val1,
    val... = new.val(...)
WHERE now() between starttime AND endtime;


This way your current record stays current and you simply insert history records.

Regards,
Richard Broersma Jr.

Hi!

Richard Broersma Jr wrote:
> This way your current record stays current and you simply insert
> history records.
Sometimes there is an obvious easy way ;) Thanks a lot!

However, I had to adapt the solution a little:
In order to avoid (undeferrable) primary key conflicts, I have to
*first* move the start time 'out of the way', then insert the new row
(with corrected start time).

For the archives:

UPDATE table SET start = start + '00:00:00.10'::interval
  WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;
  -- 2037-12-XX being my definition of  'eternity'

INSERT INTO table(id,start,stop,val,...)
  VALUES (old.id, old.start - '00:00:00.10'::interval, now(), old.val ...);

UPDATE table SET start = now(), val=...
  WHERE table.id = old.id AND table.stop > '2037-12-01
00:00:00'::timestamp without time zone;

(The short time that (errorously) two periods 'overlap' has to be taken
into account for checks!)


HPO

On 10/16/07, Hans-Peter Oeri <hp@oeri.ch> wrote:
> Any help or link greatly appreciated

You could have main_table + audit_table, store all main_table changes
on audit_table using triggers, thus main_table always has the
"current" data. Then create the view on top of main_table.