compatible 9.5–15
This extension recreates the behavior defined in
SQL:2016 (originally in
SQL:2011) around periods and tables with SYSTEM VERSIONING. The idea
is to figure out all the rules that PostgreSQL would like to adopt
(there are some details missing in the standard) and to allow earlier
versions of PostgreSQL to simulate the behavior once the feature is
finally integrated.
A period is a definition on a table which specifies a name and two columns. The period’s name cannot be the same as any column name of the table.
-- Standard SQL
CREATE TABLE example (
id bigint,
start_date date,
end_date date,
PERIOD FOR validity (start_date, end_date)
);Defining a period constrains the two columns such that the start column’s value must be strictly inferior to the end column’s value, and that both columns be non-null. The period’s value includes the start value but excludes the end value. A period is therefore very similar to PostgreSQL’s range types, but a bit more restricted.
Since extensions cannot modify PostgreSQL’s grammar, we use functions, views, and triggers to get as close to the same thing as possible.
CREATE TABLE example (
id bigint,
start_date date,
end_date date
);
SELECT periods.add_period('example', 'validity', 'start_date', 'end_date');Periods may be part of PRIMARY KEYs and UNIQUE constraints.
-- Standard SQL
CREATE TABLE example (
id bigint,
start_date date,
end_date date,
PERIOD FOR validity (start_date, end_date),
UNIQUE (id, validity WITHOUT OVERLAPS)
);CREATE TABLE example (
id bigint,
start_date date,
end_date date
);
SELECT periods.add_period('example', 'validity', 'start_date', 'end_date');
SELECT periods.add_unique_key('example', ARRAY['id'], 'validity');The extension will create a unique constraint over all of the columns
specified and the two columns of the period given. It will also create
an exclusion constraint using gist to implement the WITHOUT OVERLAPS
part of the constraint. The function also takes optional parameters if
you already have such a constraint that you would like to use.
-- Standard SQL
CREATE TABLE example (
id bigint,
start_date date,
end_date date,
PERIOD FOR validity (start_date, end_date),
CONSTRAINT example_pkey PRIMARY KEY (id, validity WITHOUT OVERLAPS)
);CREATE TABLE example (
id bigint,
start_date date,
end_date date,
CONSTRAINT example_pkey PRIMARY KEY (id, start_date, end_date)
);
SELECT periods.add_period('example', 'validity', 'start_date', 'end_date');
SELECT periods.add_unique_key('example', ARRAY['id'], 'validity', unique_constraint => 'example_pkey');Unique constraints may only contain one period.
If you can have unique keys with periods, you can also have foreign keys pointing at them.
SELECT periods.add_foreign_key('example2', 'ARRAY[ex_id]', 'validity', 'example_id_validity');In this example, we give the name of the unique key instead of listing out the referenced columns as you would in normal SQL.
The SQL standard allows syntax for updating or deleting just a portion
of a period. Rows are inserted as needed for the portions not being
updated or deleted. Yes, that means a simple DELETE statement can
actually INSERT rows!
-- Standard SQL
UPDATE example
FOR PORTION OF validity FROM '...' TO '...'
SET ...
WHERE ...;
DELETE FROM example
FOR PORTION OF validity FROM '...' TO '...'
WHERE ...;When updating a portion of a period, it is illegal to modify either of
the two columns contained in the period. This extension uses a view with
an INSTEAD OF trigger to figure out what portion of the period you
would like to modify, and issue the correct DML on the underlying table
to do the job.
In order to use this feature, the table must have a primary key.
UPDATE example__for_portion_of_validity
SET ...,
start_date = ...,
end_date = ...
WHERE ...;We see no way to simulate deleting portions of periods, alas.
The SQL standard provides for several predicates on periods. We have implemented them as inlined functions for the sake of completeness but they require specifying the start and end column names instead of the period name.
-- Standard SQL and this extension's equivalent
-- "t" and "u" are tables with respective periods "p" and "q".
-- Both periods have underlying columns "s" and "e".
WHERE t.p CONTAINS 42
WHERE periods.contains(t.s, t.e, 42)
WHERE t.p CONTAINS u.q
WHERE periods.contains(t.s, t.e, u.s, u.e)
WHERE t.p EQUALS u.q
WHERE periods.equals(t.s, t.e, u.s, u.e)
WHERE t.p OVERLAPS u.q
WHERE periods.overlaps(t.s, t.e, u.s, u.e)
WHERE t.p PRECEDES u.q
WHERE periods.precedes(t.s, t.e, u.s, u.e)
WHERE t.p SUCCEEDS u.q
WHERE periods.succeeds(t.s, t.e, u.s, u.e)
WHERE t.p IMMEDIATELY PRECEDES u.q
WHERE periods.immediately_precedes(t.s, t.e, u.s, u.e)
WHERE t.p IMMEDIATELY SUCCEEDS u.q
WHERE periods.immediately_succeeds(t.s, t.e, u.s, u.e)If the period is named SYSTEM_TIME, then special rules apply. The type
of the columns must be date, timestamp without time zone, or
timestamp with time zone; and they are not modifiable by the user. In
the SQL standard, the start column is GENERATED ALWAYS AS ROW START
and the end column is GENERATED ALWAYS AS ROW END. This extension uses
triggers to set the start column to transaction_timestamp() and the
end column is always 'infinity'.
Note: It is generally unwise to use anything but timestamp with time zone because changes in the TimeZone configuration paramater or
even just Daylight Savings Time changes can distort the history. Even
when only using UTC, we recommend the timestamp with time zone type.
-- Standard SQL
CREATE TABLE example (
id bigint PRIMARY KEY,
value text,
PERIOD FOR system_time (row_start, row_end)
);CREATE TABLE example (
id bigint PRIMARY KEY,
value text
);
SELECT periods.add_system_time_period('example', 'row_start', 'row_end');Note that the columns in this special case need not exist. They will be
created both by the SQL standard and by this extension. A special
function is provided as a convenience, but add_period can also be
called.
It might be desirable to prevent some columns from updating the
SYSTEM_TIME values. For example, perhaps your users table has a
column last_login which gets updated all the time and you don’t want
to generate a new historical row (see below) for just that. Ideally such
a column would be in its own table, but if not then it can be excluded
with an optional parameter:
SELECT periods.add_system_time_period(
'example',
excluded_column_names => ARRAY['foo', 'bar']);Excluded columns can be define after the fact, as well.
SELECT periods.set_system_time_period_excluded_columns(
'example',
ARRAY['foo', 'bar']);This functionality is not present in the SQL standard.
This special SYSTEM_TIME period can be used to keep track of changes
in the table.
-- Standard SQL
CREATE TABLE example (
id bigint PRIMARY KEY,
value text,
PERIOD FOR system_time (row_start, row_end)
) WITH SYSTEM VERSIONING;CREATE TABLE example (
id bigint PRIMARY KEY,
value text
);
SELECT periods.add_system_time_period('example', 'row_start', 'row_end');
SELECT periods.add_system_versioning('example');This instructs the system to keep a record of all changes in the table. We use a separate history table for this. You can create the history table yourself and instruct the extension to use it if you want to do things like add partitioning.
The SQL standard extends the FROM and JOIN clauses to allow
specifying a point in time, or even a range of time (shall we say a
period of time?) for which we want the data. This only applies to base
tables and so this extension implements them through inlined functions.
-- Standard SQL and this extension's equivalent
SELECT * FROM t FOR system_time AS OF '...';
SELECT * FROM t__as_of('...');
SELECT * FROM t FOR system_time FROM '...' TO '...';
SELECT * FROM t__from_to('...', '...');
SELECT * FROM t FOR system_time BETWEEN '...' AND '...';
SELECT * FROM t__between('...', '...');
SELECT * FROM t FOR system_time BETWEEN SYMMETRIC '...' AND '...';
SELECT * FROM t__between_symmetric('...', '...');The history table as well as the helper functions all follow the
ownership and access privileges of the base table. It is not possible to
change the privileges independently. The history data is also read-only.
In order to trim old data, SYSTEM VERSIONING must be suspended.
BEGIN;
SELECT periods.drop_system_versioning('t');
GRANT DELETE ON TABLE t TO CURRENT_USER;
DELETE FROM t_history WHERE system_time_end < now() - interval '1 year';
SELECT periods.add_system_versioning('t');
COMMIT;The privileges are automatically fixed when system versioning is resumed.
The SQL Standard does not say much about what should happen to a table with system versioning when the table is altered. This extension prevents you from dropping objects while system versioning is active, and other changes will be prevented in the future. The suggested way to make changes is:
BEGIN;
SELECT periods.drop_system_versioning('t');
ALTER TABLE t ...;
ALTER TABLE t_history ...;
SELECT periods.add_system_versioning('t');
COMMIT;It is up to you to make sure you alter the history table in a way that is compatible with the main table. Re-activating system versioning will verify this.
This extension is pretty much feature complete, but there are still many aspects that need to be handled.
Performance for the temporal queries should be already very similar to what we can expect from a native implementation in PostgreSQL.
Unique keys should also be as performant as a native implementation,
except that two indexes are needed instead of just one. One of the goals
of this extension is to fork btree to a new access method that handles
the WITHOUT OVERLAPS and then patch that back into PostgreSQL when
periods are added.
Foreign key performance should mostly be reasonable, except perhaps when validating existing data. Some benchmarks would be helpful here.
Performance for the DDL stuff isn’t all that important, but those functions will likely also be rewritten in C, if only to start being the patch to present to the PostgreSQL community.
Contributions are very much welcome!
If you would like to help implement the missing features, optimize them, rewrite them in C, and especially modify btree; please don’t hesitate to do so.
This project adheres to the PostgreSQL Community Code of Conduct.
Released under the PostgreSQL License.
The project would like extend special thanks to:
- Christoph Berg for Debian packaging,
- Devrim Gündüz for RPM packaging, and
- Mikhail Titov for Appveyor and Windows support.