{"id":2332,"date":"2014-09-24T12:58:40","date_gmt":"2014-09-24T12:58:40","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=2332"},"modified":"2014-09-24T12:58:40","modified_gmt":"2014-09-24T12:58:40","slug":"postgres-time-travelling-debugger","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/postgres-time-travelling-debugger\/","title":{"rendered":"Postgres: Time Travelling Debugger"},"content":{"rendered":"

Imagine you\u2019re an engineer doing phone support for Netflix. The movies they show change regularly:<\/p>\n

\"netflix7\"<\/p>\n

There are various reasons for this \u2013 Netflix suddenly thinks you like period pieces, or they get into a contract dispute with one of their vendors.<\/p>\n

\"netflix9\"<\/p>\n

As a support engineer, this presents a set of communication challenges, which inspire some technical solutions. Customers often ask questions like \u201cwhy is this different than a week ago?\u201d It’s a reasonable question, but very time consuming to answer. <\/p>\n

\"netflix10\"<\/p>\n

To make this easier, we need to store the audit history of this data<\/a>, and write a view that shows the full information available about a movie over time. <\/p>\n

For this example, we define a simple schema, with movie and their associated licenses.<\/p>\n

\ncreate table movies (\n  id int primary key, \n  title text);\n\ncreate table licenses (\n  id int primary key,\n  movie_id int references movies (id),\n  title text, \n  start_date timestamp, \n  end_date timestamp\n);\n<\/pre>\n
The example database maintains audit history, which stores every change that has happened over time:<\/p>\n
\ncreate table movies$a (\n  id int, \n  title text, \n  audit_txid bigint,\n  audit_date timestamp with time zone);\n\ncreate table licenses$a (\n  id int,\n  movie_id int references movies (id),\n  title text, \n  start_date timestamp, \n  end_date timestamp,\n  audit_txid bigint,\n  audit_date timestamp with time zone\n);\n<\/pre>\n
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 <@ operator is 'contains')\n\n\n\n
\nselect\n  id, movie_title, license_start, license_end\nfrom\n  movie_history_vw\nwhere \n  effective <@ (now() - interval '1 day')\n<\/pre>\n
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\u2019s effective until infinity:<\/p>\n
\nselect\n  m.id, m.title,\n  tstzrange(\n    coalesce(m.audit_date, '-infinity'), \n    coalesce(lead(m.audit_date) over w_m, 'infinity'),\n    '[)'\n  ) movie_effective \nfrom movies$a m\nwindow w_m as (partition by m.id order by m.audit_date asc)\n<\/pre>\n
\"debugger-1\"<\/p>\n
\nselect\n  l.id, l.title, movie_id,\n  tstzrange(\n    coalesce(l.audit_date, '-infinity'), \n    coalesce(lead(l.audit_date) over w_l, 'infinity'),\n    '[)'\n  ) license_effective  \nfrom licenses$a l\nwindow w_l as (partition by l.id order by l.audit_date asc)\n<\/pre>\n
\"debugger-2\"<\/p>\n
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:<\/p>\n
\nwith movies_history as (...),\nlicenses_history as (...)\nselect m.id, m.title, l.id, l.title,\n  movie_effective,\n  coalesce(l.license_effective, '[-infinity,infinity]') license_effective\nfrom movies_history m\nleft join licenses_history l\non l.movie_id = m.id\n<\/pre>\n
\"debugger-3\"<\/p>\n
Now we can filter the results to only include rows where the license and movie were effective at the same time. <\/p>\n
The “&&” operator checks for overlapping intervals, and the “*” operator finds the minimum interval:<\/p>\n
\nwith movies_history as (...),\n     licenses_history as (...),\n     joined_history as (...)\nselect \n  movie_id, movie_title, license_id, license_title,\n  movie_effective * license_effective effective\nfrom joined_history\nwhere movie_effective && license_effective\n<\/pre>\n
\"debugger-4\"<\/p>\n
From this, we can now generate the query as desired, which filters to values effeffective at a specific time yesterday:<\/p>\n
\ncreate view movie_history_vw as \nwith movies_history as (...),\n     licenses_history as (...),\n     joined_history as (...)\nwhere movie_effective @> (now() - interval '1 day')\n<\/pre>\n
\"debugger-5\"<\/p>\n
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:<\/p>\n
\ncreate or replace function movie_history(timestamp with time zone) \nreturns setof movie_history_vw as \n$$\ndeclare\n  r record;\n  query text;\nbegin\n  return query\n    select * \n    from movie_history_vw \n    where license_effective @> $1 \nend\n$$ language 'plpgsql';\n\nselect * from movie_history(now() - interval '1 day');\n<\/pre>\n
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. <\/p>\n
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:<\/p>\n
\ntable_sql := \n  format(\n    '-- audit table creation\ncreate table %s$a\n     as select t.*, \n       null::text(1) audit_action,\n       null::text audit_request,\n       null::bigint audit_txid,\n       null::text audit_user, \n       null::timestamp audit_date\n     from %I t \n     where 0 = 1',\n     table_name,\n     table_name\n);\n<\/pre>\n
The full solution to generate the audit tables and the history view is included in the wingspan-auditing<\/a> library.<\/p>\n","protected":false},"excerpt":{"rendered":"
Imagine you\u2019re an engineer doing phone support for Netflix. The movies they show change regularly: There are various reasons for this \u2013 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 … <\/p>\n
Continue reading “Postgres: Time Travelling Debugger”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4,6],"tags":[85,140,149,155,157,437],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t