xocolatl/periods

A SYSTEM_TIME period tracks when rows were valid in the system. The columns used for this (start and end) are managed by triggers: the start column is set to transaction_timestamp() and the end column is set to 'infinity'.

Best Practice: Use timestamp with time zone for these columns to avoid issues with TimeZone or Daylight Savings changes.

Setup

Use periods.add_system_time_period to create the columns and the period. You can also exclude specific columns from triggering a new historical row when they are updated.

System Versioning

To enable full system versioning (which maintains a separate history table for all changes), call periods.add_system_versioning after setting up the SYSTEM_TIME period.

-- 1. Create table
CREATE TABLE example (
    id bigint PRIMARY KEY,
    value text
);

-- 2. Add SYSTEM_TIME period (columns are created automatically)
SELECT periods.add_system_time_period('example', 'row_start', 'row_end');

-- 3. (Optional) Exclude columns from triggering history updates
SELECT periods.set_system_time_period_excluded_columns('example', ARRAY['last_login']);

-- 4. Enable System Versioning to create a history table
SELECT periods.add_system_versioning('example');

System-versioned tables involve a base table and a history table.

Access Control

• The history table and helper functions inherit the ownership and privileges of the base table.
• History data is read-only.
• To perform maintenance on history data (like trimming old rows), you must temporarily suspend system versioning.

Altering Tables

To prevent errors during DDL operations, you should suspend system versioning, perform your ALTER TABLE commands on both the base table and the history table, and then re-enable versioning.

Warning: Re-activating versioning will verify that the history table remains compatible with the base table.

-- How to safely trim old history data
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;

-- How to safely alter a versioned table
BEGIN;
SELECT periods.drop_system_versioning('t');
ALTER TABLE t ADD COLUMN new_col text;
ALTER TABLE t_history ADD COLUMN new_col text;
SELECT periods.add_system_versioning('t');
COMMIT;

You can include a period in PRIMARY KEY or UNIQUE constraints to ensure that no two rows for the same identifier have overlapping periods.

Use periods.add_unique_key to implement this. The extension creates a unique constraint over the specified columns and the period columns, and uses a GIST exclusion constraint to enforce the WITHOUT OVERLAPS logic.

If you have an existing constraint (like a primary key) that you want to use as the basis for the period uniqueness, provide its name via the unique_constraint parameter.

-- Basic unique key with a period
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');

-- Using an existing primary key
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');

The extension provides inlined functions to perform temporal logic comparisons. Because the extension cannot change SQL syntax, you must pass the start and end columns explicitly instead of using the period name.