temporal_tables（时间表）

使用方法

安装插件

CREATE EXTENSION temporal_tables;

创建系统时间段时间表

temporal_tables插件使用一个通用的触发器函数来维护系统时间段时间表的行为：

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

该函数必须在系统时间段时间表上的INSERT、UPDATE或DELETE之前触发，需要指定以下参数：

示例

1. 准备基础数据，创建employees表：

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

2. 将employees表修改为系统时间段时间表，添加一个系统时间段列：

   ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

3. 创建一张包含表中归档行的时间表，最简单的创建方法是使用LIKE语句：

   CREATE TABLE employees_history (LIKE employees);

   说明

   时间表不必与原表结构相同。例如，您希望归档原行的某些列，同时忽略其他列，或者时间表可能包含一些在原表中不必要的有用信息。时间表的必须满足的两个要求如下：

   • 时间表必须包含与原表中同名且数据类型相同的系统时间段列。

   • 如果时间表和原表都包含希望归档的列，则该列的数据类型在这两个表中必须相同。

4. 在employees表上创建一个触发器，将其与时间表连接。

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

插入数据

将数据插入系统时间段时间表，类似于将数据插入常规表。

示例

1. 将以下数据于2006年8月8日插入到employees表中：

   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. 查询employees表和其时间表中的数据。

   • employees表：

     SELECT * FROM employees;

     返回结果如下：

            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：

     SELECT * FROM employees_history;

     返回结果如下，表格数据为空：

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

sys_period列的开始时间表示该行变为当前的时间。触发器通过使用CURRENT_TIMESTAMP值生成此值，该值表示当前事务中执行的第一个数据更改语句的时间。

更新数据

当更新系统时间段时间表中行的列值时，触发器会将旧行的副本插入关联的时间表。如果单个事务对同一行进行多次更新，则只生成一个历史行。

示例

1. 于2007年2月27日在更新employees表中如下数据：

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

2. 查询employees表和其时间表中的数据。

   • employees表：

     SELECT * FROM employees;

     返回结果如下：

            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：

     SELECT * FROM employees_history;

     返回结果如下，更新employees表的记录：

          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)

更新冲突与时间调整

当多个事务更新同一行时，可能会发生更新冲突。例如，事务A和B同时对表employees执行语句：

在T1和T2插入之后，系统时间段时间表employees包含以下数据：

时间表employees_history为空。在T4时，触发器必须将行的sys_period列的开始设置为T1，并将以下行插入时间表：

然而，T2>T1，因此该行无法插入。在这种情况下，T4时的更新会以SQL STATE 22000失败。为了避免这种失败，您可以将触发器的"adjust"参数设置为true。然后在T4时，sys_period列的开始时间设置为T2加上一个微小的时间间隔（通常为1 微秒）。在这个调整和事务A完成之后，employees表如下所示：

时间表employees_history包含以下数据：

删除数据

从系统时间段时间表中删除数据时，触发器会将行添加到关联的时间表中。

示例

1. 于2012年12月24日从employees表中删除如下数据：

   DELETE FROM employees WHERE name = 'Helmholtz Watson';

2. 查询employees表和其时间表中的数据。

   • employees表：

     SELECT * FROM employees;

     返回结果如下：

          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：

     SELECT * FROM employees_history;

     返回结果如下，更新employees表的记录：

            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)

高级用法

除了使用CURRENT_TIMESTAMP，您可以为版本控制触发器设置自定义系统时间，这对于基于记录系统时间的数据创建数据仓库非常有用。

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

要将其恢复为默认行为，可以使用NULL作为参数调用该函数。

SELECT set_system_time(NULL);

在事务内执行set_system_time函数，如果该事务稍后被中止，则所有更改都会被撤销。如果事务提交，则更改将一直持续到会话结束。

使用继承创建时间表

在之前示例中，使用LIKE语句创建时间表，有时使用继承更好。例如：

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

然后创建系统时间段时间表：

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

维护时间表

时间表总是在增长，因此会消耗越来越多的存储空间。您可以使用以下方式维护时间表空间。

• 删除时间表中的旧数据：

  • 定期删除时间表中的旧数据。

  • 使用分区并从时间表中分离旧分区。

  • 维护旧行的规则：

    • 删除超过一定年龄的行。

    • 仅保留某行的最新N个版本。

    • 当从系统时间段时间表中删除相应行时，删除时间表内对应行。

    • 删除满足指定业务规则的行。

• 为时间表设置另一个表空间，以将其转移到其他存储上，详细介绍请参考冷数据分层存储。

使用系统时间段时间表进行数据审计

可以使用系统时间段时间表进行数据审计。例如，您可以添加以下触发器来保存修改或删除当前行的用户：

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();

相关参考

temporal_tables的详细说明，请参考temporal_tables。