Imagine you’re an engineer doing phone support for Netflix. The movies they show change regularly:
There are various reasons for this – Netflix suddenly thinks you like period pieces, or they get into a contract dispute with one of their vendors.
As a support engineer, this presents a set of communication challenges, which inspire some technical solutions. Customers often ask questions like “why is this different than a week ago?” It’s a reasonable question, but very time consuming to answer.
To make this easier, we need to store the audit history of this data, and write a view that shows the full information available about a movie over time.
For this example, we define a simple schema, with movie and their associated licenses.
create table movies (
id int primary key,
title text);
create table licenses (
id int primary key,
movie_id int references movies (id),
title text,
start_date timestamp,
end_date timestamp
);
The example database maintains audit history, which stores every change that has happened over time:
create table movies$a (
id int,
title text,
audit_txid bigint,
audit_date timestamp with time zone);
create table licenses$a (
id int,
movie_id int references movies (id),
title text,
start_date timestamp,
end_date timestamp,
audit_txid bigint,
audit_date timestamp with time zone
);
Given this, we want to write a view that shows everything about a movie, including an ‘effective’ time interval. If we include an interval in the view, we can filter to a specific time, as shown below (the select id, movie_title, license_start, license_end from movie_history_vw where effective
The first step is to generate effective date ranges for rows in the movie and license tables. The ‘tsrange’ function creates intervals from the time of a change and the subsequent change – the last argument shows that it is closed on one end and open on the other. If we find the most recent change, we say it’s effective until infinity:
select
m.id, m.title,
tstzrange(
coalesce(m.audit_date, '-infinity'),
coalesce(lead(m.audit_date) over w_m, 'infinity'),
'[)'
) movie_effective
from movies$a m
window w_m as (partition by m.id order by m.audit_date asc)
select
l.id, l.title, movie_id,
tstzrange(
coalesce(l.audit_date, '-infinity'),
coalesce(lead(l.audit_date) over w_l, 'infinity'),
'[)'
) license_effective
from licenses$a l
window w_l as (partition by l.id order by l.audit_date asc)
Once we have effective date ranges for each relevant entity, we need to join the two histories on the foreign key relationship. Since the license is optional, we set it’s effective range to all of time, to make the next step easier:
with movies_history as (...),
licenses_history as (...)
select m.id, m.title, l.id, l.title,
movie_effective,
coalesce(l.license_effective, '[-infinity,infinity]') license_effective
from movies_history m
left join licenses_history l
on l.movie_id = m.id
Now we can filter the results to only include rows where the license and movie were effective at the same time.
The “&&” operator checks for overlapping intervals, and the “*” operator finds the minimum interval:
with movies_history as (...),
licenses_history as (...),
joined_history as (...)
select
movie_id, movie_title, license_id, license_title,
movie_effective * license_effective effective
from joined_history
where movie_effective && license_effective
From this, we can now generate the query as desired, which filters to values effeffective at a specific time yesterday:
create view movie_history_vw as
with movies_history as (...),
licenses_history as (...),
joined_history as (...)
where movie_effective @> (now() - interval '1 day')
If you want to provide more parameters, it can be valuable to embed this query in a stored procedure, and use the stored procedure as a view that takes parameters, as shown below:
create or replace function movie_history(timestamp with time zone)
returns setof movie_history_vw as
$$
declare
r record;
query text;
begin
return query
select *
from movie_history_vw
where license_effective @> $1
end
$$ language 'plpgsql';
select * from movie_history(now() - interval '1 day');
For the full solution to this, we need to generate the above views – once it includes dozens of tables, it is far too complex to hand-write.
If you want to do this with plpgsql, the format function works nicely, as it supports multi-line strings and can format identifiers for you:
table_sql :=
format(
'-- audit table creation
create table %s$a
as select t.*,
null::text(1) audit_action,
null::text audit_request,
null::bigint audit_txid,
null::text audit_user,
null::timestamp audit_date
from %I t
where 0 = 1',
table_name,
table_name
);
The full solution to generate the audit tables and the history view is included in the wingspan-auditing library.