Date: 2020-05-28
Time: 14:00–14:45
Room: Stream 2
Level: Intermediate
I have a patch in progress to add SQL:2011 valid time support to Postgres, so that you can more easily record a history of things that change over time. It lets you define temporal primary and foreign keys and issue temporal UPDATE and DELETE commands. Although the SQL:2011 standard introduces a new concept called PERIODs, I've based the Postgres features on our own built-in ranges---and plan to support PERIODs too (hopefully before I get to PGCon).
Since I spoke last year about temporal databases in general, this year I want to focus on the specific features I've been building: how they work and how they are implemented. First I'll discuss the user-facing behavior of these features, as well as some tricky questions around triggers and where you can use PERIODs. I'll show how my decisions were informed by comparing other RDBMSes like MariaDB, Oracle, IBM DB2, and MS SQL Server. Second I'll discuss how I implemented the changes: how temporal PKs & FKs work, and how I added temporal DML to the execution pipeline. I'll include an overview of a new abstract type called multiranges---something I needed for temporal foreign keys that could also be useful for other purposes.
The following slides have been made available for this session: