temporal_tables(时间表)

PolarDB PostgreSQL版支持temporal_tables插件,能够实现系统时间段的时间表功能。在向数据表中插入一行时,系统会自动生成时间段的开始值和结束值,当更新或删除数据表中的一行时,旧行会被归档到另一个表中,称为历史表,即本文所讲述的时间表。

前提条件

支持的PolarDB PostgreSQL版的版本如下:

PostgreSQL 14(内核小版本14.13.25.0及以上)

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本号:

  • PostgreSQL 14

    SELECT version();
  • PostgreSQL 11

    SHOW polar_version;

使用方法

安装插件

CREATE EXTENSION temporal_tables;

创建系统时间段时间表

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

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

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

参数名称

描述

system_period_column_name

系统时间段列名。

history_table_name

时间表名。

adjust

当向数据表中插入一行时,系统会自动生成时间段的开始值和结束值,该参数用于检查这个时间段的结束值是否大于开始值。取值范围如下:

  • false:当结束值大于开始值时,则会报错,无法写入时间表。

  • true:结束值默认设置为开始值加上间隔时间值。

示例

  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执行语句:

Time

事务A

事务B

T1

INSERT INTO employees (name, salary) VALUES ('Bernard Marx', 10000);

T2

INSERT INTO employees (name, salary) VALUES ('Lenina Crowne', 7000);

T3

COMMIT;

T4

UPDATE employees SET salary = 6800 WHERE name = 'Lenina Crowne';

T5

INSERT INTO employees (name, salary) VALUES ('Helmholtz Watson', 18500);

T6

COMMIT;

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

name

department

salary

sys_period

Bernard Marx

Hatchery and Conditioning Centre

10000

[T1, )

Lenina Crowne

Hatchery and Conditioning Centre

7000

[T2, )

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

name

department

salary

sys_period

Lenina Crowne

Hatchery and Conditioning Centre

7000

[T2, T1)

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

name

department

salary

sys_period

Bernard Marx

Hatchery and Conditioning Centre

10000

[T1, )

Lenina Crowne

Hatchery and Conditioning Centre

6800

[T2 + delta, )

Helmholtz Watson

College of Emotional Engineering

18500

[T1, )

时间表employees_history包含以下数据:

name

department

salary

sys_period

Lenina Crowne

Hatchery and Conditioning Centre

7000

[T2, T2 + delta)

删除数据

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

示例

  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