32. 使用触发器跟踪编辑历史记录¶

生产数据库的一个常见需求是跟踪历史记录：数据在两个日期之间是如何变化的，谁进行了更改，以及更改发生在哪里？一些 GIS 系统通过在客户端界面中包含更改管理来跟踪更改，但这会给编辑工具带来很多 **复杂性**。

使用数据库和触发器系统，可以将历史记录跟踪添加到任何表中，同时保持对主表的简单“直接编辑”访问。

历史记录跟踪通过维护一个历史记录表来工作，该表记录每次编辑

如果创建了记录，则记录添加时间和添加人。
如果删除了记录，则记录删除时间和删除人。
如果更新了记录，则添加删除记录（用于旧状态）和创建记录（用于新状态）。

32.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 索引非常有效地进行索引，正如我们将在下面展示的那样。这使得历史查询非常高效。

32.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 的当前状态导入历史表，以便我们有一个起点来跟踪历史。请注意，我们填写了创建时间和创建用户，但将时间范围的结束时间和删除用户信息留空。

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 操作。首先，我们创建触发器函数，然后将它们绑定到表作为触发器。

对于插入，我们只需在历史表中添加一条新记录，其中包含创建时间/用户。

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();

对于删除，我们只需将当前活动的历史记录（具有空删除时间的记录）标记为已删除。

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();

32.3. 编辑表¶

现在历史表已启用，我们可以对主表进行编辑，并观察历史表中出现的日志条目。

请注意这种基于数据库的历史方法的强大功能：无论使用什么工具进行编辑，无论是 SQL 命令行、基于 Web 的 JDBC 工具，还是像 QGIS 这样的桌面工具，历史记录都始终如一地跟踪。

32.3.1. SQL 编辑¶

让我们将两条名为“Cumberland Walk”的街道改为更时尚的“Cumberland Wynde”。

更新这两条街道会导致原始街道在历史表中被标记为已删除，删除时间为现在，并添加两条带有新名称的新街道，添加时间为现在。您可以检查历史记录。

32.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)

我们还可以创建仅显示特定用户添加的内容的视图，例如

CREATE OR REPLACE VIEW nyc_streets_postgres AS
  SELECT * FROM nyc_streets_history
    WHERE created_by = 'postgres';

PostGIS 简介

32. 使用触发器跟踪编辑历史记录¶

32.1. 使用 TSTZRANGE¶

32.2. 构建历史表¶

32.3. 编辑表¶

32.3.1. SQL 编辑¶

32.4. 查询历史表¶

32.5. 另请参阅¶

PostGIS 简介

32. 使用触发器跟踪编辑历史记录¶

32.1. 使用 TSTZRANGE¶

32.2. 构建历史表¶

32.3. 编辑表¶

32.3.1. SQL 编辑¶

32.4. 查询历史表¶

32.5. 另请参阅¶

导航