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

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

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)

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

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

PostGIS 简介

34. 使用触发器跟踪编辑历史¶

34.1. 使用 TSTZRANGE¶

34.2. 构建历史记录表¶

34.3. 编辑表¶

34.3.1. SQL 编辑¶

34.4. 查询历史记录表¶

34.5. 另请参阅¶

PostGIS 简介

34. 使用触发器跟踪编辑历史¶

34.1. 使用 TSTZRANGE¶

34.2. 构建历史记录表¶

34.3. 编辑表¶

34.3.1. SQL 编辑¶

34.4. 查询历史记录表¶

34.5. 另请参阅¶

导航