PolarDB:temporal_tables

Basic usage

Install the extension

CREATE EXTENSION temporal_tables;

Create a system-period temporal table

The temporal_tables extension uses a common trigger function to manage a system-period temporal table:

versioning(<system_period_column_name>, <history_table_name>, <adjust>)

The function must be fired before an INSERT, UPDATE, or DELETE operation is performed on the system-period temporal table. The following stable describes the parameters of the function.

Example

1. Prepare the basic data and create a table named employees.

   CREATE TABLE employees
   (
     name text NOT NULL PRIMARY KEY,
     department text,
     salary numeric(20, 2)
   );

2. Add a system period column to the employees table to change the table to a system-period temporal table.

   ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

3. Create a history table that contains the archived rows of the employees table. The easiest way to create the table is by using the LIKE clause:

   CREATE TABLE employees_history (LIKE employees);

   Note

   A history table does not require the same structure as the original table. For example, you want to archive some columns of an original row but ignore others, or a history table may contain some useful information that is unnecessary in the original table. A history table must meet the following requirements:

   • A history table must contain a system period column that has the same name and data type as the original table.

   • If the history table and the original table contain a column, the data type of the column must be the same in the two tables.

4. Create a trigger on the employees table and associate the trigger with the history table.

   CREATE TRIGGER versioning_trigger
   BEFORE INSERT OR UPDATE OR DELETE ON employees
   FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period',
                                             'employees_history',
                                             true);

Insert data

You can insert data into the system-period temporal table in a similar manner as you insert data into a regular table

Example

1. The following data is inserted into the employees table on August 8, 2006.

   INSERT INTO employees (name, department, salary)
   VALUES ('Bernard Marx', 'Hatchery and Conditioning Centre', 10000);
   INSERT INTO employees (name, department, salary)
   VALUES ('Lenina Crowne', 'Hatchery and Conditioning Centre', 7000);
   INSERT INTO employees (name, department, salary)
   VALUES ('Helmholtz Watson', 'College of Emotional Engineering', 18500);

2. Query the data in the employees table and its history table.

   • employees table:

     SELECT * FROM employees;

     Result:

            name       |            department            |  salary  |         sys_period          
     ------------------+----------------------------------+----------+-----------------------------
      Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00",)
      Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
     (3 rows)

   • employees_history table:

     SELECT * FROM employees_history;

     The following result is returned. The history table is empty.

      name | department | salary | sys_period 
     ------+------------+--------+------------
     (0 rows)

The start time of the sys_period column represents the time when the row is written into the table. The trigger generates the value by using the CURRENT_TIMESTAMP value which indicates the time when the first data change statement was executed in the current transaction.

Update data

If you update the value of a column in a row of the system-period temporal table, the trigger inserts a copy of the old row into the associated history table. If a single transaction updates the same row multiple times, only one history row is generated.

Example

1. The following data was updated in the employees table on February 27, 2007.

   UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';

2. Query the data in the employees table and its history table.

   • employees table:

     SELECT * FROM employees;

     Result:

            name       |            department            |  salary  |         sys_period          
     ------------------+----------------------------------+----------+-----------------------------
      Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
      Bernard Marx     | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
     (3 rows)

   • employees_history table:

     SELECT * FROM employees_history;

     The following result is returned. The records in the employees_history table are updated.

          name     |            department            |  salary  |                     sys_period                      
     --------------+----------------------------------+----------+-----------------------------------------------------
      Bernard Marx | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
     (1 row)

Update conflicts and time adjustment

Update conflicts can occur when multiple transactions update the same row. For example, transactions A and B execute statements on the employees table at the same time:

After data is inserted at T1 and T2, the employees table contains the following data:

The employee_history table is empty. At T4, the trigger must set the start value of the sys_period column of the row to T1 and insert the following row into the history table:

However, T2 is greater than T1. Therefore, the row cannot be inserted. In this case, the update at T4 fails and an SQL STATE 22000 error message is returned. To avoid such failures, you can set the adjust parameter of the trigger to true. Then, at T4, set the start time of the sys_period column to T2 plus a small time interval (usually 1 microsecond). After the adjustment and the completion of transaction A, the employees table contains the following data:

The employees_history table contains the following data:

Delete data

When you delete a row from a system-period temporal table, the trigger adds the row to the associated history table.

Example

1. The following data was deleted from the employees table on December 24, 2012.

   DELETE FROM employees WHERE name = 'Helmholtz Watson';

2. Query the data in the employees table and its history table.

   • employees table:

     SELECT * FROM employees;

     Result:

          name      |            department            |  salary  |         sys_period          
     ---------------+----------------------------------+----------+-----------------------------
      Lenina Crowne | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Bernard Marx  | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
     (2 rows)

   • employees_history table:

     SELECT * FROM employees_history;

     The following result is returned. The records in the employees_history table are updated.

            name       |            department            |  salary  |                     sys_period                      
     ------------------+----------------------------------+----------+-----------------------------------------------------
      Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00","2012-12-24 00:00:00+00")
     (2 rows)

Advanced usage

Instead of using the CURRENT_TIMESTAMP value, you can specify a custom system time for versioning triggers. This can help you create a data warehouse from an existing system that has recorded its own timestamps.

SELECT set_system_time('1985-08-08 06:42:00+08');

To remove the custom settings and restore the default system time, call the function with NULL as its argument.

SELECT set_system_time(NULL);

If the set_system_time function is invoked within a transaction that is later aborted, all changes are reverted. If the transaction is committed, the changes persist until the end of the session.

Use inheritance to create a system-period temporal table

In the preceding example, the LIKE clause is used to create a history table. In some cases, you can use inheritance to create a system-period temporal table. Example:

CREATE TABLE employees_history
(
  name text NOT NULL,
  department text,
  salary numeric(20, 2),
  sys_period tstzrange NOT NULL
);

Create a system-period temporal table by using inheritance:

CREATE TABLE employees (PRIMARY KEY(name)) INHERITS (employees_history);

Maintain the history table

History tables are always growing and consume an increasing amount of storage. You can use the following methods to prune old data from a history table:

• Delete old data from the history table:

  • Periodically delete old data from the history table.

  • Use partitioning and detach old partitions from the history table.

  • Use the following rules to prune old rows:

    • Prune rows older than a specific age.

    • Retain only the latest N versions of a row.

    • Prune rows when a corresponding row is deleted from the system-period temporal table.

    • Prune rows that meet the specified business rules.

• You can also configure another tablespace for a history table to move the table to a cheaper storage. For more information, see Cold data tiered storage.

Use system-period temporal tables for data audit

You can use system-period temporal tables for data audit. For example, you can add the following triggers to save users that modified or deleted the current row:

CREATE FUNCTION employees_modify()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_modified = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_modify
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE employees_modify();

CREATE FUNCTION employees_delete()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_deleted = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_delete
BEFORE INSERT ON employees_history
FOR EACH ROW EXECUTE PROCEDURE employees_delete();

References

For more information about temporal_tables, see temporal_tables.