temporal tables

_README.md

(Untested) Postgres SQL + PLPGSQL implementation of temporal tables.

Goals:

• No dynamic or parameterized SQL after setup
• Can be uninstalled after setup
• SQL+PLPGSQL for environments with strict or limited extension support
• Quacks like a temporal table implementation
• Doesn't drift into the future
• Uses two columns instead of range column, no loss in data when lower and upper are equal

Improvements

• Procedures could cast input table to a regclass

Getting started

1. Run deploy.sql to enable temporal table DDL generation.
2. Historicize tables with CALL temporal_tables.historicize('your_schema', 'your_table');
3. Optional: Run revert.sql to remove temporal table DDL generation.
4. Enjoy!

Need to revert?

1. If revert.sql was used, re-run deploy.sql
2. Drop history features per-table with CALL temporal_tables.drop_historicize('your_schema', 'your_table');
3. Optional: Drop the history table too with DROP TABLE your_schema.your_table_history;
4. Run revert.sql to remove temporal table DDL generation.

deploy.sql

BEGIN;

CREATE SCHEMA temporal_tables;

--
-- Begin Identifiers
CREATE OR REPLACE FUNCTION temporal_tables.history_table_identifier (IN
  in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_history_table_identifier$
BEGIN
  RETURN in_temporal_table || '_history';
END;
$quote_history_table_identifier$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.history_table_identifier(text) IS '
Returns history table identifier for input temporal table identifier.
WARNING: Return value is unescaped, unquoted, and potentially longer than an OID.
';

CREATE OR REPLACE FUNCTION temporal_tables.function_identifier (IN
  in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_function_identifier$
BEGIN
  RETURN in_temporal_table || '_tt_fn';
END;
$quote_function_identifier$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.function_identifier(text) IS '
Returns temporal table trigger function identifier for input temporal table identifier.
WARNING: Return value is unescaped, unquoted, and potentially longer than an OID.
';

CREATE OR REPLACE FUNCTION temporal_tables.trigger_identifier (IN
  in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_trigger_identifier$
BEGIN
  RETURN in_temporal_table || '_tt_tr';
END;
$quote_trigger_identifier$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.trigger_identifier(text) IS '
Returns temporal table trigger identifier for input temporal table identifier.
WARNING: Return value is unescaped, unquoted, and potentially longer than an OID.
';

--
-- Step 1: Alter temporal table
CREATE OR REPLACE FUNCTION temporal_tables.fmt_alter_temporal_table (IN
  in_schema TEXT, IN in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_fmt_alter_temporal_table$
BEGIN
  RETURN format('ALTER TABLE ONLY %1$I.%2$I
  ADD COLUMN sysrange_lower timestamptz(3),
  ADD COLUMN sysrange_upper timestamptz(3);', in_schema, in_temporal_table);
END;
$quote_fmt_alter_temporal_table$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.fmt_alter_temporal_table(text, text) IS '
Formats DDL: Add sysrange_lower and sysrange_upper columns of timestamptz type to input table.
';

CREATE OR REPLACE PROCEDURE temporal_tables.alter_temporal_table (IN in_schema
  TEXT, IN in_temporal_table TEXT)
  AS $quote_alter_temporal_table$
BEGIN
  -- Not necessary to check identifier length, not creating new identifiers
  EXECUTE temporal_tables.fmt_alter_temporal_table(in_schema, in_temporal_table);
END;
$quote_alter_temporal_table$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE temporal_tables.alter_temporal_table(text, text) IS '
Add sysrange_lower and sysrange_upper columns of timestamptz type to input table.
';

--
-- Step 2: Create history table
CREATE OR REPLACE FUNCTION temporal_tables.fmt_create_history_table (IN
  in_schema TEXT, IN in_history_table TEXT, IN in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_fmt_create_history_table$
BEGIN
  RETURN format('CREATE TABLE %1$I.%2$I (
    LIKE %1$I.%3$I
  );', in_schema, in_history_table, in_temporal_table);
END;
$quote_fmt_create_history_table$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.fmt_create_history_table(text, text, text)
  IS '
Formats DDL: Create history table LIKE temporal table. Temporal table must have sysrange columns.
';

CREATE OR REPLACE PROCEDURE temporal_tables.create_history_table (IN in_schema
  TEXT, IN in_temporal_table TEXT)
  AS $quote_create_history_table$
BEGIN
  -- History table OID too long
  IF octet_length(temporal_tables.history_table_identifier(in_temporal_table)) > 63 THEN
    RAISE string_data_right_truncation;
  END IF;
  EXECUTE temporal_tables.fmt_create_history_table(in_schema,
    temporal_tables.history_table_identifier(in_temporal_table),
    in_temporal_table);
END;
$quote_create_history_table$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE temporal_tables.create_history_table(text, text) IS '
Create history table LIKE temporal table. Temporal table must have sysrange columns.
';

--
-- Step 3: Create function
CREATE OR REPLACE FUNCTION temporal_tables.fmt_create_function (IN in_schema
  TEXT, IN in_function TEXT, IN in_history_table TEXT)
  RETURNS TEXT
  AS $quote_fmt_create_function$
BEGIN
  RETURN format('CREATE OR REPLACE FUNCTION %1$I.%2$I ()
    RETURNS TRIGGER
    AS $$
  BEGIN
    IF TG_WHEN != ''BEFORE'' OR TG_LEVEL != ''ROW'' THEN
      RAISE trigger_protocol_violated;
    END IF;
    IF TG_OP = ''DELETE'' THEN
      OLD.sysrange_upper = current_timestamp(3);
      INSERT INTO %1$I.%3$I
        VALUES (OLD.*);
      RETURN OLD;
    ELSIF TG_OP = ''INSERT'' THEN
      NEW.sysrange_lower = current_timestamp(3);
      NEW.sysrange_upper = ''infinity'';
      RETURN NEW;
    ELSIF TG_OP = ''UPDATE'' THEN
      OLD.sysrange_upper = current_timestamp(3);
      NEW.sysrange_lower = OLD.sysrange_upper;
      NEW.sysrange_upper = ''infinity'';
      INSERT INTO %1$I.%3$I
        VALUES (OLD.*);
      RETURN NEW;
    ELSE
      RAISE trigger_protocol_violated;
    END IF;
  END;
  $$
  LANGUAGE plpgsql;', in_schema, in_function, in_history_table);
END;
$quote_fmt_create_function$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.fmt_create_function(text, text, text) IS '
Formats DDL: Create temporal table trigger function.
';

CREATE OR REPLACE PROCEDURE temporal_tables.create_function (IN in_schema TEXT,
  IN in_temporal_table TEXT)
  AS $quote_create_function$
BEGIN
  -- Function OID too long
  IF octet_length(temporal_tables.function_identifier(in_temporal_table)) > 63 THEN
    RAISE string_data_right_truncation;
  END IF;

  EXECUTE temporal_tables.fmt_create_function(in_schema,
    temporal_tables.function_identifier(in_temporal_table),
    temporal_tables.history_table_identifier(in_temporal_table));
END;
$quote_create_function$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE temporal_tables.create_function(text, text) IS '
Create temporal table trigger function.
';

--
-- Step 4: Create trigger
CREATE OR REPLACE FUNCTION temporal_tables.fmt_create_trigger (IN in_trigger
  TEXT, IN in_schema TEXT, IN in_temporal_table TEXT, IN in_function TEXT)
  RETURNS TEXT
  AS $quote_fmt_create_trigger$
BEGIN
  RETURN format('CREATE OR REPLACE TRIGGER %1$I
    BEFORE DELETE OR INSERT OR UPDATE ON %2$I.%3$I
    FOR EACH ROW
    EXECUTE FUNCTION %2$I.%4$I ();', in_trigger, in_schema, in_temporal_table, in_function);
END;
$quote_fmt_create_trigger$
LANGUAGE plpgsql
IMMUTABLE;

COMMENT ON FUNCTION temporal_tables.fmt_create_trigger(text, text, text, text)
  IS '
Formats DDL: Create temporal table trigger.
';

CREATE OR REPLACE PROCEDURE temporal_tables.create_trigger (IN in_schema TEXT,
  IN in_temporal_table TEXT)
  AS $quote_create_trigger$
BEGIN
  -- Trigger OID too long
  IF octet_length(temporal_tables.trigger_identifier(in_temporal_table)) > 63 THEN
    RAISE string_data_right_truncation;
  END IF;

  EXECUTE temporal_tables.fmt_create_trigger(
    -- pgformatter breaks without this comment. Don't remove...
    temporal_tables.trigger_identifier(in_temporal_table), in_schema,
      in_temporal_table,
      temporal_tables.function_identifier(in_temporal_table));
END;
$quote_create_trigger$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE temporal_tables.create_trigger(text, text) IS '
Create temporal table trigger.
';

--
-- Steps 1..4
CREATE OR REPLACE PROCEDURE temporal_tables.historicize (IN in_schema TEXT, IN
  in_temporal_table TEXT)
  AS $quote_historicize$
BEGIN
  CALL temporal_tables.alter_temporal_table(in_schema, in_temporal_table);
  CALL temporal_tables.create_history_table(in_schema, in_temporal_table);
  CALL temporal_tables.create_function(in_schema, in_temporal_table);
  CALL temporal_tables.create_trigger(in_schema, in_temporal_table);
END;
$quote_historicize$
LANGUAGE plpgsql;

--
-- Step 6: Drop trigger
CREATE OR REPLACE FUNCTION temporal_tables.fmt_drop_trigger (IN in_trigger
  TEXT, IN in_schema TEXT, IN in_temporal_table TEXT)
  RETURNS TEXT
  AS $quote_fmt_drop_trigger$
BEGIN
  RETURN format('DROP TRIGGER %1$I ON %2$I.%3$I;', in_trigger, in_schema, in_temporal_table);
END;
$quote_fmt_drop_trigger$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE PROCEDURE temporal_tables.drop_trigger (IN in_schema TEXT, IN
  in_temporal_table TEXT)
  AS $quote_drop_trigger$
BEGIN
  EXECUTE temporal_tables.fmt_drop_trigger(
    -- pgformatter breaks without this comment. Don't remove...
    temporal_tables.trigger_identifier(in_temporal_table), in_schema, in_temporal_table);
END;
$quote_drop_trigger$
LANGUAGE plpgsql;

--
-- Step 7: Drop function
CREATE OR REPLACE FUNCTION temporal_tables.fmt_drop_function (IN in_schema
  TEXT, IN in_function TEXT)
  RETURNS TEXT
  AS $quote_fmt_drop_function$
BEGIN
  RETURN format('DROP FUNCTION %1$I.%2$I;', in_schema, in_function);
END;
$quote_fmt_drop_function$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE PROCEDURE temporal_tables.drop_function (IN in_schema TEXT,
  IN in_temporal_table TEXT)
  AS $quote_drop_function$
BEGIN
  EXECUTE temporal_tables.fmt_drop_function(in_schema,
    temporal_tables.function_identifier(in_temporal_table));
END;
$quote_drop_function$
LANGUAGE plpgsql;

--
-- Steps 6..7: Drop trigger and function
CREATE OR REPLACE PROCEDURE temporal_tables.drop_historicize (IN in_schema
  TEXT, IN in_temporal_table TEXT)
  AS $quote_drop_historicize$
BEGIN
  CALL temporal_tables.drop_trigger(in_schema, in_temporal_table);
  CALL temporal_tables.drop_function(in_schema, in_temporal_table);
END;
$quote_drop_historicize$
LANGUAGE plpgsql;

COMMIT;

revert.sql

BEGIN;

DROP FUNCTION temporal_tables.history_table_identifier;

DROP FUNCTION temporal_tables.function_identifier;

DROP FUNCTION temporal_tables.trigger_identifier;

DROP FUNCTION temporal_tables.fmt_alter_temporal_table;

DROP PROCEDURE temporal_tables.alter_temporal_table;

DROP FUNCTION temporal_tables.fmt_create_history_table;

DROP PROCEDURE temporal_tables.create_history_table;

DROP FUNCTION temporal_tables.fmt_create_function;

DROP PROCEDURE temporal_tables.create_function;

DROP FUNCTION temporal_tables.fmt_create_trigger;

DROP PROCEDURE temporal_tables.create_trigger;

DROP PROCEDURE temporal_tables.historicize;

DROP FUNCTION temporal_tables.fmt_drop_trigger;

DROP PROCEDURE temporal_tables.drop_trigger;

DROP FUNCTION temporal_tables.fmt_drop_function;

DROP PROCEDURE temporal_tables.drop_function;

DROP PROCEDURE temporal_tables.drop_historicize;

DROP SCHEMA temporal_tables;

COMMIT;

verify.sql

BEGIN;

SELECT
  has_function_privilege('temporal_tables.history_table_identifier(text)', 'execute');

SELECT
  temporal_tables.history_table_identifier('verify');

SELECT
  1 / cast(temporal_tables.history_table_identifier('demo') =
    'demo_history' AS INTEGER);

SELECT
  has_function_privilege('temporal_tables.function_identifier(text)', 'execute');

SELECT
  1 / cast(temporal_tables.function_identifier('demo') = 'demo_tt_fn'
    AS INTEGER);

SELECT
  has_function_privilege('temporal_tables.trigger_identifier(text)', 'execute');

SELECT
  1 / cast(temporal_tables.trigger_identifier('demo') =
    'demo_tt_tr' AS INTEGER);

SELECT
  has_function_privilege('temporal_tables.fmt_alter_temporal_table(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.alter_temporal_table(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.fmt_create_history_table(text, text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.create_history_table(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.fmt_create_function(text, text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.create_function(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.fmt_create_trigger(text, text, text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.create_trigger(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.historicize(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.fmt_drop_trigger(text, text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.drop_trigger(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.fmt_drop_function(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.drop_function(text, text)', 'execute');

SELECT
  has_function_privilege('temporal_tables.drop_historicize(text, text)', 'execute');

ROLLBACK;