如何在PostgreSQL中存储表的历史记录

Question

场景

我们需要在PostgreSQL中存储记录历史，这样当将记录插入或更新到主表（例如：pets）时，它会自动备份到历史表格（pets_history）。

理想情况下，我们需要基于主表的模式生成历史表，而不需要任何人工干预。

INSERT INTO pets(name, species) VALUES ('Meowth', 'Cat')

pets:
+---+------------+-------------+
|id | name       | species     |
+---+------------+-------------+
| 1 | Meowth     | Cat         |
+---+------------+-------------+

触发器应自动将记录插入到pets_history中：

pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
+----+--------+-----------+---------+

当对宠物进行更新以更改我的猫的名称从 Meowth 更改为 Persian 时。例如：

<code><code>UPDATE pets SET name = 'Persian' WHERE id = 1;
</code></code>

<code><code>pets:
+---+------------+-------------+
|id | name       | species     |
+---+------------+-------------+
| 1 | Persian    | Cat         |
+---+------------+-------------+
</code></code>

我希望最终得到以下结果...

<code><code>pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
| 2  | 1      | Persian   | Cat     |
+----+--------+-----------+---------+
</code></code>

当其他列/字段被添加到pets表中时，例如：color。


<code><code>pets:
+---+--------+---------+-------+
|id | name   | species | color |
+---+--------+---------+-------+
| 1 | Meowth | Cat     | cream |
+---+--------+---------+-------+
</code></code>

我们希望这可以自动反映在pets_history表中：

<code><code>pets_history:
+----+--------+---------+---------+-------+
| id | ref_id | name    | species | color |
+----+--------+---------+---------+-------+
| 1  | 1      | Meowth  | Cat     | null  |
| 2  | 1      | Persian | Cat     | null  |
| 3  | 1      | Persian | Cat     | cream |
+----+--------+---------+---------+-------+
</code></code>

如果有人知道如何在PostgreSQL本地或其他方式中实现这一点，请分享。

我们查看了这个问题/答案Implementing history of PostgreSQL table，它部分地解决了这个挑战，但它不会自动创建_history表。

- RobStallion

Answer 1

你可以使用 to_jsonb 将整行作为JSON对象存储在历史表中。在这种情况下，您无需关心在历史表中添加新列，因为值的键将是列名。

宠物表

CREATE TABLE public.pets
(
  id serial NOT NULL,
  name text,
  species text,
  PRIMARY KEY (id)
);

宠物历史表格。

CREATE TABLE public.h_pets
(
  id serial NOT NULL,
  target_row_id integer NOT NULL,
  executed_operation integer NOT NULL,
  operation_executed_at timestamp without time zone NOT NULL DEFAULT now(),
  data_after_executed_operation jsonb,
  PRIMARY KEY (id)
);

添加历史表行的函数

CREATE OR REPLACE FUNCTION public.on_content_change()
  RETURNS trigger
  LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    target_history_table TEXT;
  BEGIN
    target_history_table := TG_ARGV[0];

    IF TG_OP = 'INSERT'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 0, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 1, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation) VALUES ($1.id, 2)',
          target_history_table
        )
        USING OLD;
      RETURN OLD;
    END IF;
  END;
$BODY$;

宠物表的触发器

CREATE TRIGGER pets_history_trigger
  BEFORE INSERT OR DELETE OR UPDATE
  ON public.pets
  FOR EACH ROW
  EXECUTE PROCEDURE public.on_content_change('h_pets');

- Yeheshuah

---

Schema把问题摆在了最前面：插入时需要知道存在哪些列。而无模式则将问题推迟到了读取时需要弄清楚存在哪些列。两种方法都有优缺点，但如果需要从历史表还原数据，我不会建议某人存储无模式JSON。 - Cypress Frankenfeld

Answer 2

根据您选择的数据库管理系统，您可能可以自动化此过程。

Postgres有两个构造可以帮助您：(1)事件触发器 - 在数据库级别的事件上触发，如创建和修改表；和(2)触发器函数 - 可以创建触发器并将其应用于表。

在高层次上，您可以这样做：

创建一个触发器函数CreateHistoryTrigger，实现在基表上插入和更新时触发并插入到相应的历史表中的逻辑。您可以通过创建基表名称、列名称和其他选项的参数来概括这个函数。当调用时，该函数将动态地设计一个触发器并将其应用于一个命名表。

创建一个事件触发器ManageHistoryTables，用于监视您感兴趣的更改，即假设您不想为每个表都创建历史表，您可以通过模式、命名约定、特定表的列表或其他适当的方式来过滤数据库事件。可能您至少想监视CREATE TABLE、ALTER TABLE和DROP TABLE。

对于CREATE TABLE或ALTER TABLE，检查系统表以查看与基表对应的历史表是否存在。如果不存在，则通过从通过查询系统表发现的基表的列属性构建创建或修改表命令，并运行该命令来创建它。

对于ALTER TABLE，如果历史表已经存在，则将其当前列定义与基表进行比较，然后通过从通过查询系统表发现的基表的列属性构建修改表命令，并运行该命令来添加/修改/删除列。

对于DROP TABLE，删除历史表（如果您想这样做的话）。

在这一点上，您已经拥有了正确的历史表结构，但是没有触发器来填充该表结构。

在您的事件触发器中，对于任何一个CREATE TABLE、ALTER TABLE或DROP TABLE，调用CreateHistoryTrigger触发器函数，传入您关心的表名和列名。该函数需要在表上CREATE OR REPLACE触发器，因为可能存在与同一表结构的先前版本不同的触发器版本。

像这样的元编程可能会有挑战性，但它也是一种有趣且“永久”/可重复使用的解决问题的方式。如果您能够将手动编写的触发器中使用的逻辑进行泛化，允许表名和列名作为参数，那么这可能并不难，您将能够在将来的项目中重复使用相同的事件触发器和触发器函数。这是您工具包中不错的一件东西。

- Chris Johnson