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 | 当向数据表中插入一行时,系统会自动生成时间段的开始值和结束值,该参数用于检查这个时间段的结束值是否大于开始值。取值范围如下:
|
示例
准备基础数据,创建employees表:
CREATE TABLE employees ( name text NOT NULL PRIMARY KEY, department text, salary numeric(20, 2) );
将employees表修改为系统时间段时间表,添加一个系统时间段列:
ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;
创建一张包含表中归档行的时间表,最简单的创建方法是使用
LIKE
语句:CREATE TABLE employees_history (LIKE employees);
说明时间表不必与原表结构相同。例如,您希望归档原行的某些列,同时忽略其他列,或者时间表可能包含一些在原表中不必要的有用信息。时间表的必须满足的两个要求如下:
时间表必须包含与原表中同名且数据类型相同的系统时间段列。
如果时间表和原表都包含希望归档的列,则该列的数据类型在这两个表中必须相同。
在employees表上创建一个触发器,将其与时间表连接。
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'employees_history', true);
插入数据
将数据插入系统时间段时间表,类似于将数据插入常规表。
示例
将以下数据于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);
查询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值生成此值,该值表示当前事务中执行的第一个数据更改语句的时间。
更新数据
当更新系统时间段时间表中行的列值时,触发器会将旧行的副本插入关联的时间表。如果单个事务对同一行进行多次更新,则只生成一个历史行。
示例
于2007年2月27日在更新employees表中如下数据:
UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';
查询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) |
删除数据
从系统时间段时间表中删除数据时,触发器会将行添加到关联的时间表中。
示例
于2012年12月24日从employees表中删除如下数据:
DELETE FROM employees WHERE name = 'Helmholtz Watson';
查询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。