34. 使用触发器追踪编辑历史¶
生产级数据库通常需要追踪历史变更:数据在两个时间点之间如何变化、由谁修改以及变更发生在何处?部分GIS系统通过在客户端界面集成变更管理来实现此功能,但这会显著增加编辑工具的复杂度。
通过数据库和触发器系统,可以为任何数据表添加历史追踪功能,同时保持对主表的"直接编辑"访问权限。
历史追踪通过维护一个历史表来记录每次编辑的操作,该表会保存:
如果创建了一条记录,则记录其添加时间及操作者。
若记录被删除,则记录其删除时间及操作者。
如果一条记录被更新,则会添加一条删除记录(记录旧状态)和一条创建记录(记录新状态)。
34.1. 使用 TSTZRANGE 类型¶
历史表采用PostgreSQL特有特性——"时间戳范围类型"——来存储每条历史记录作为"当前有效记录"的时间区间。对于特定地理要素的所有历史记录,其时间戳范围均满足非重叠但连续相邻的特性。
新记录的时间范围将从 now()
开始,并保持终点开放,从而使该范围涵盖从当前时间到未来的所有时间。
SELECT tstzrange(current_timestamp, NULL);
tstzrange
------------------------------------
["2021-06-01 14:49:40.910074-07",)
同理,已删除记录的时间范围将被更新,将当前时间作为该时间范围的终止点。
相比起检索一对时间戳,时间范围查询更为简便——这得益于开放式时间范围(从起点无限延伸至未来)的特性。我们将主要使用范围「包含」运算符 @>
来实现此类查询。
-- Does the range of "ten minutes ago to the future" include now?
-- It should! :)
--
SELECT tstzrange(current_timestamp - '10m'::interval, NULL) @> current_timestamp;
时间范围字段可通过GIST索引实现高效检索——这与空间数据的索引机制类似(下文将具体演示)。该技术能显著提升历史查询效率。
34.2. 历史表构建方案¶
利用这些信息,可重构编辑表在任意时间点的状态。本例中,我们将为 nyc_streets 表添加历史追踪功能。
首先,创建 nyc_streets_history 历史表,用于存储所有街道数据的编辑历史记录。除继承原表 nyc_streets 的全部字段外,还需新增五个专用字段。
**hid**(历史表主键字段)
created_by 记录创建者标识
deleted_by 执行删除操作的数据库用户
valid_range (有效时间范围):记录作为"当前有效数据"存在的时间区间
需注意,我们不会真正删除历史表中的记录,仅标记其不再属于编辑表当前状态的时间节点。
DROP TABLE IF EXISTS nyc_streets_history; CREATE TABLE nyc_streets_history ( hid SERIAL PRIMARY KEY, gid INTEGER, id FLOAT8, name VARCHAR(200), oneway VARCHAR(10), type VARCHAR(50), geom GEOMETRY(MultiLinestring,26918), valid_range TSTZRANGE, created_by VARCHAR(32), deleted_by VARCHAR(32) ); CREATE INDEX nyc_streets_history_geom_x ON nyc_streets_history USING GIST (geom); CREATE INDEX nyc_streets_history_tstz_x ON nyc_streets_history USING GIST (valid_range);
接着,我们将当前活跃表**nyc_streets**的状态导入历史表,以此作为历史追溯的基准点。需注意,此处仅填充创建时间和创建用户信息,而时间范围的结束时间及删除操作者字段留空(NULL)。
INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) SELECT gid, id, name, oneway, type, geom, tstzrange(now(), NULL), current_user FROM nyc_streets;
当前需在活跃表上创建三个触发器,分别响应 INSERT、DELETE 和 UPDATE 操作。首先创建触发器函数,随后将其绑定至目标表。
对于插入操作(INSERT),我们仅需在历史表中新增一条记录,并标注创建时间及创建者信息。
CREATE OR REPLACE FUNCTION nyc_streets_insert() RETURNS trigger AS $$ BEGIN INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) VALUES (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom, tstzrange(current_timestamp, NULL), current_user); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_insert_trigger AFTER INSERT ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_insert();
对于删除操作,我们只需将当前活跃的历史记录(即删除时间为 NULL 的记录)标记为已删除状态。
CREATE OR REPLACE FUNCTION nyc_streets_delete() RETURNS trigger AS $$ BEGIN UPDATE nyc_streets_history SET valid_range = tstzrange(lower(valid_range), current_timestamp), deleted_by = current_user WHERE valid_range @> current_timestamp AND gid = OLD.gid; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_delete_trigger AFTER DELETE ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_delete();
对于更新操作,系统会执行两步处理:首先将当前活跃的历史记录标记为已删除状态,随后插入一条新记录以存储更新后的数据状态。
CREATE OR REPLACE FUNCTION nyc_streets_update() RETURNS trigger AS $$ BEGIN UPDATE nyc_streets_history SET valid_range = tstzrange(lower(valid_range), current_timestamp), deleted_by = current_user WHERE valid_range @> current_timestamp AND gid = OLD.gid; INSERT INTO nyc_streets_history (gid, id, name, oneway, type, geom, valid_range, created_by) VALUES (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom, tstzrange(current_timestamp, NULL), current_user); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER nyc_streets_update_trigger AFTER UPDATE ON nyc_streets FOR EACH ROW EXECUTE PROCEDURE nyc_streets_update();
34.3. 表数据编辑操作¶
历史表启用后,对主表执行的所有编辑操作将自动生成日志条目并记录至历史表中。
请注意这种基于数据库的历史记录机制的强大之处:无论通过何种工具进行编辑(SQL命令行、基于Web的JDBC工具,还是QGIS等桌面工具),系统均能保持历史记录的一致性追踪。
34.3.1. SQL 数据编辑操作¶
将两条名为"Cumberland Walk"的街道更名为更优雅的"Cumberland Wynde":
更新这两条街道将导致原始街道在历史表中被标记为已删除,并记录删除时间为当前时间,同时添加两条具有新名称的新街道,并记录添加时间为当前时间。你可以检查历史记录:
34.4. 历史表查询方法¶
历史表的核心价值在于实现"时间旅行"查询。要回溯特定时间点**T**的数据状态,需构建包含以下要素的查询:
需包含所有在时间点T之前创建且尚未删除的记录
所有在时间点 T 之前创建,但在 T **之后**才被删除的记录。
我们可以利用这一逻辑创建历史数据查询或视图。假设您的测试编辑操作均在最近几分钟内完成,现在创建一个显示10分钟前(即编辑开始前的原始数据状态)的历史表视图。
-- Records with a valid range that includes 10 minutes ago
-- are the ones valid at that moment.
CREATE OR REPLACE VIEW nyc_streets_ten_min_ago AS
SELECT * FROM nyc_streets_history
WHERE valid_range @> (now() - '10min'::interval)
We can also create views that show just what a particular user has added, for example:
CREATE OR REPLACE VIEW nyc_streets_postgres AS
SELECT * FROM nyc_streets_history
WHERE created_by = 'postgres';