PostgreSQL表的历史记录实现

Question

我想要实现PostgreSQL表的更改历史记录。该表定义如下：

CREATE TABLE "ps_counters"
(
    "psid" integer NOT NULL,
    "counter" bigint[] NOT NULL
);

我希望历史记录表格的样子像这样：

CREATE TABLE "ps_counters_history"
(
    "timestamp" timestamp NOT NULL,
    "psid" integer NOT NULL,
    "counter" bigint[] NOT NULL
);

我需要一个触发器和一个存储过程，当 ps_counters 表中有任何变更（插入或更新）时，将其插入到 ps_counters_history 表中。但另外为了防止 ps_counters_history 表变得过大，我希望在每个月对 ps_counters_history 表进行分区。

- bobeff

---

太大了吗？每个月有多少次ps_counters更改？ - jarlh

好的，你已经非常详细地描述了解决方案。你卡在哪个部分了？ - IMSoP

审计触发器和分区 - Abelisto

Answer 1

我已经成功地实现了它。

CREATE TABLE "ps_counters_history"
(
  "id" serial PRIMARY KEY,
  "timestamp" timestamp NOT NULL DEFAULT clock_timestamp(),
  "psid" integer NOT NULL,
  "counter" bigint[] NOT NULL
);

CREATE OR REPLACE FUNCTION ps_counters_history_trigger()
  RETURNS trigger AS
$BODY$
  DECLARE
    table_name text;
  BEGIN
    table_name := 'ps_counters_history_' || to_char(CURRENT_DATE, 'yyyy_mm');
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = table_name)
    THEN
      EXECUTE 'CREATE TABLE IF NOT EXISTS ' || table_name ||
              ' () INHERITS (ps_counters_history);';
    END IF;
    EXECUTE 'INSERT INTO ' || table_name ||
            '(psid, counter) VALUES ($1.psid, $1.counter);' USING NEW;
    RETURN NEW;
  END
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER ps_counters_history_trigger
AFTER INSERT OR UPDATE ON ps_counters FOR EACH ROW
EXECUTE PROCEDURE ps_counters_history_trigger();

- bobeff

---

仅提供几点建议： 1）通常表格应该有一个主键 2）now()返回事务开始时的时间，clock_timestamp()返回实际时间 3）execute可以与参数一起使用：EXECUTE 'INSERT INTO ' || table_name || '(psid, counter) VALUES ($1.psid, $1.counter);' USING NEW; - Abelisto

提醒一下，如果两个并发事务同时运行您的“如果不存在则创建”逻辑，它将会出现错误。尽管进行了“如果不存在”的检查，但您仍会收到“表已存在”的错误提示。 - Craig Ringer

如果我在if语句内部使用CREATE TABLE IF NOT EXISTS而不是CREATE TABLE，是否可以防止错误发生？您如何建议解决这个问题？ - bobeff

@bobeff 你好，你找到解决方案了吗？在你上面的问题中，我认为最好是提前创建历史表，例如在数据库连接初始化时，这样在触发器内部你只需要插入行而不是创建表（解决了使用CREATE TABLE/ CREATE TABLE IF NOT EXISTS时的并发问题）。 - kataras

为什么不提前创建分区表呢？如果您可能在一年前就已经准备好了一个表，那么在插入时处理表的创建有什么意义呢？ - gavenkoa