README.sql
------------------------------------------------------
-- Temporal schema for an example "countries" relation
--
-- https://github.com/ifad/chronomodel
--
create schema temporal; -- schema containing all temporal tables
create schema history; -- schema containing all history tables
-- Current countries data - nothing special
--
create table temporal.countries (
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
-- https://www.postgresql.org/docs/9.0/ddl-inherit.html#DDL-INHERIT-CAVEATS
--
create table history.countries (
hid serial primary key,
validity tsrange,
recorded_at timestamp not null default timezone('UTC', now()),
constraint overlapping_times exclude using gist ( id with =, validity with && )
) inherits ( temporal.countries );
-- Inherited primary key
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 ( validity );
-- 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 entry point of the temporal triggers.
--
-- SELECT - return only current data
--
create view public.countries as select * from only temporal.countries;
-- INSERT - insert data both in the current data table and in the history one.
--
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.
--
-- 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 the update doesn't change the data, it is skipped and the trigger
-- returns NULL.
--
-- By default, history is not recorded if only the updated_at field
-- is changed.
--
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;
_old := row(old.name);
_new := row(new.name);
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 chronomodel_update
instead of update on temporal.countries
for each row execute procedure chronomodel_countries_update();
-- DELETE - save the current data in the history and eventually delete the
-- data from the temporal table.
--
-- 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());
delete from history.countries
where id = old.id and validity = tsrange(_now, null);
update history.countries set valid_to = _now
where id = old.id and upper_inf(validity);
delete from only temporal.countries
where temporal.id = old.id;
return old;
end;
$$ language plpgsql;
create trigger chronomodel_delete
instead of delete on temporal.countries
for each row execute procedure chronomodel_countries_delete();
-- EOF