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';