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