README.sql in chrono_model-0.5.3 vs README.sql in chrono_model-0.8.0

@@ -7,141 +7,155 @@
 create schema history;  -- schema containing all history tables
 
 -- Current countries data - nothing special
 --
 create table temporal.countries (
-  id   serial primary key,
-  name varchar
+  id         serial primary key,
+  name       varchar,
+  updated_at timestamptz
 );
 
 -- Countries historical data.
 --
 -- Inheritance is used to avoid duplicating the schema from the main table.
 -- Please note that columns on the main table cannot be dropped, and other caveats
 -- http://www.postgresql.org/docs/9.0/static/ddl-inherit.html#DDL-INHERIT-CAVEATS
 --
 create table history.countries (
-
   hid         serial primary key,
-  valid_from  timestamp not null,
-  valid_to    timestamp not null default '9999-12-31',
+  validity    tsrange,
   recorded_at timestamp not null default timezone('UTC', now()),
 
-  constraint from_before_to check (valid_from < valid_to),
+  constraint overlapping_times exclude using gist ( id with =, validity with && )
 
-  constraint overlapping_times exclude using gist (
-    box(
-      point( date_part( 'epoch', valid_from), id ),
-      point( date_part( 'epoch', valid_to - interval '1 millisecond'), id )
-    ) with &&
-  )
 ) inherits ( temporal.countries );
 
 -- Inherited primary key
-create index country_inherit_pkey on history.countries ( id )
+create index country_inherit_pkey on history.countries ( id );
 
 -- Snapshot of data at a specific point in time
-create index country_snapshot on history.countries USING gist (
-  box(
-    point( date_part( 'epoch', valid_from ), 0 ),
-    point( date_part( 'epoch', valid_to   ), 0 )
-  )
-)
+create index country_snapshot on history.countries USING gist ( validity );
 
--- Used by the rules queries when UPDATE'ing and DELETE'ing
-create index country_valid_from  on history.countries ( valid_from )
-create index country_valid_to    on history.countries ( valid_from )
-create index country_recorded_at on history.countries ( id, valid_to )
+-- Used by the trigger functions when UPDATE'ing and DELETE'ing
+create index country_lower_validity on history.countries ( lower(validity) )
+create index country_upper_validity on history.countries ( upper(validity) )
+create index country_recorded_at    on history.countries ( id, valid_to )
 
 -- Single instance whole history
 create index country_instance_history on history.countries ( id, recorded_at )
 
 
--- The countries view, what the Rails' application ORM will actually CRUD on, and
--- the core of the temporal updates.
+-- The countries view, what the Rails' application ORM will actually CRUD on,
+-- and the entry point of the temporal triggers.
 --
 -- SELECT - return only current data
 --
-create view public.countries as select *, xmin as __xid from only temporal.countries;
+create view public.countries as select * from only temporal.countries;
 
--- INSERT - insert data both in the current data table and in the history table.
+-- INSERT - insert data both in the current data table and in the history one.
 --
--- A trigger is required if there is a serial ID column, as rules by
--- design cannot handle the following case:
+create or replace function public.chronomodel_countries_insert() returns trigger as $$
+  begin
+    if new.id is null then
+      new.id = nextval('temporal.countries_id_seq');
+    end if;
+
+    insert into temporal.countries ( id, name, updated_at )
+    values ( new.id, new.name, new.updated_at );
+
+    insert into history.countries (id, name, updated_at, validity )
+    values ( new.id, new.name, new.updated_at, tsrange(timezone('utc', now()), null) );
+
+    return new;
+  end;
+$$ language plpgsql;
+
+create trigger chronomodel_insert
+  instead of insert on public.countries
+  for each row execute procedure public.chronomodel_countries_insert();
+
+-- UPDATE - set the last history entry validity to now, save the current data
+-- in a new history entry and update the temporal table with the new data.
 --
---   * INSERT INTO ... SELECT: if using currval(), all the rows
---     inserted in the history will have the same identity value;
+-- If a row in the history with the current ID and current timestamp already
+-- exists, update it with new data. This logic makes possible to "squash"
+-- together changes made in a transaction in a single history row.
 --
---   * if using a separate sequence to solve the above case, it may go
---     out of sync with the main one if an INSERT statement fails due
---     to a table constraint (the first one is nextval()'ed but the
---     nextval() on the history one never happens)
+-- If the update doesn't change the data, it is skipped and the trigger
+-- returns NULL.
 --
--- So, only for this case, we resort to an AFTER INSERT FOR EACH ROW trigger.
+-- By default, history is not recorded if only the updated_at field
+-- is changed.
 --
--- Ref: GH Issue #4.
---
-create rule countries_ins as on insert to public.countries do instead (
+create function chronomodel_countries_update() returns trigger as $$
+  declare _now timestamp;
+  declare _hid integer;
+  declare _old record;
+  declare _new record;
+  begin
+  
+    if old is not distinct from new then
+      return null;
+    end if;
 
-  insert into temporal.countries ( name ) values ( new.name );
-  returning ( id, new.name, xmin )
-);
+    _old := row(old.name);
+    _new := row(new.name);
 
-create or replace function temporal.countries_ins() returns trigger as $$
-  begin
-    insert into history.countries ( id, name, valid_from )
-    values ( currval('temporal.countries_id_seq'), new.name, timezone('utc', now()) );
-    return null;
+    if _old is not distinct from new then
+      update only temporal.countries set ( name, updated_at ) = ( new.name, new.updated_at ) where id = old.id
+      return new;
+    end if;
+
+    _now := timezone('utc', now());
+    _hid := null;
+
+    select hid into _hid from history.countries where id = old.id and lower(validity) = _now;
+
+    if _hid is not null then
+      update history.countries set ( name, updated_at ) = ( new.name ) where hid = _hid;
+    else
+      update history.countries set validity = tsrange(lower(validity), _now)
+      where id = old.id and upper_inf(validity);
+
+      insert into history.countries ( id, name, updated_at, validity )
+      values ( old.id, new.name, new.updated_at, tsrange(_now, null) );
+    end if;
+
+    update only temporal.countries set ( name ) = ( new.name ) where id = old.id;
+
+    return new;
   end;
 $$ language plpgsql;
 
-create trigger history_ins after insert on temporal.countries_ins()
-  for each row execute procedure temporal.countries_ins();
+create trigger chronomodel_update
+  instead of update on temporal.countries
+  for each row execute procedure chronomodel_countries_update();
 
--- UPDATE - set the last history entry validity to now, save the current data in
--- a new history entry and update the current table with the new data.
--- In transactions, create the new history entry only on the first statement,
--- and update the history instead on subsequent ones.
+-- DELETE - save the current data in the history and eventually delete the
+-- data from the temporal table.
 --
-create rule countries_upd_first as on update to countries
-where old.__xid::char(10)::int8 <> (txid_current() & (2^32-1)::int8)
-do instead (
-  update history.countries
-     set valid_to = timezone('UTC', now())
-   where id = old.id and valid_to = '9999-12-31';
+-- The first DELETE is required to remove history for records INSERTed and
+-- DELETEd in the same transaction.
+--
+create or replace function chronomodel_countries_delete() returns trigger as $$
+  declare _now timestamp;
+  begin
+    _now := timezone('utc', now());
 
-  insert into history.countries ( id, name, valid_from ) 
-  values ( old.id, new.name, timezone('UTC', now()) );
+    delete from history.countries
+    where id = old.id and validity = tsrange(_now, null);
 
-  update only temporal.countries
-     set name = new.name
-   where id = old.id
-);
-create rule countries_upd_next as on update to countries do instead (
-  update history.countries
-     set name = new.name
-   where id = old.id and valid_from = timezone('UTC', now())
+    update history.countries set valid_to = _now
+    where id = old.id and upper_inf(validity);
 
-  update only temporal.countries
-     set name = new.name
-   where id = old.id
-)
+    delete from only temporal.countries
+    where temporal.id = old.id;
 
--- DELETE - save the current data in the history and eventually delete the data
--- from the current table. Special case for records INSERTed and DELETEd in the
--- same transaction - they won't appear at all in history.
---
-create rule countries_del as on delete to countries do instead (
-  delete from history.countries
-   where id = old.id
-     and valid_from = timezone('UTC', now())
-     and valid_to   = '9999-12-31'
+    return old;
+  end;
+$$ language plpgsql;
 
-  update history.countries
-    set   valid_to = now()
-    where id = old.id and valid_to = '9999-12-31';
-
-  delete from only temporal.countries
-  where temporal.countries.id = old.id
-);
+create trigger chronomodel_delete
+  instead of delete on temporal.countries
+  for each row execute procedure chronomodel_countries_delete();
 
 -- EOF