postgresql - 通过触发器强制执行行不变性

Question

我有一个表，其定义类似于以下内容(为清楚起见进行了压缩):

CREATE TABLE fns(
  id serial,
  start_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_date timestamptz,
  name text NOT NULL,
  parent_id integer,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES fns(id),
  UNIQUE(name)
);

当 UPDATE 发生时，我希望被“更新”的行的 end_date 设置为 CURRENT_TIMESTAMP 并有一个新的创建的行(基于旧行)，其 start_date 设置为 CURRENT_TIMESTAMP。例如:

UPDATE之前

| id |              start_date | end_date |  name | parent_id |
|----|-------------------------|----------|-------|-----------|
|  1 | April, 01 2015 00:00:00 |   (null) | fns_a |    (null) |

UPDATE后的期望状态

| id |              start_date |                end_date |        name | parent_id |
|----|-------------------------|-------------------------|-------------|-----------|
|  1 | April, 01 2015 00:00:00 | April, 02 2015 00:00:00 | fns_a [old] |    (null) |
|  2 | April, 02 2015 00:00:00 |                  (null) |       fns_a |         1 |

我遇到了 name 列的唯一约束问题。这是我的触发器的当前状态:

CREATE OR REPLACE FUNCTION enfore_fns_immutability() RETURNS trigger AS $func$
BEGIN
  -- 'Turn off' old record.
  OLD.end_date = CURRENT_TIMESTAMP;
  OLD.name = OLD.name || ' [old]';

  -- Create the new record.
  INSERT INTO fns(start_date, name, parent_id)
    VALUES(CURRENT_TIMESTAMP, NEW.name, OLD.id); -- <-- unique violation

  RETURN OLD;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER tg_fns_bi
  BEFORE UPDATE ON fns
  FOR EACH ROW
  EXECUTE PROCEDURE enforce_fns_immutability();

据我所知，这是失败的，因为 OLD.name 的更新尚未发生，因为包含事务尚未提交。我正在努力想办法解决它，但感觉必须有一个优雅的解决方案!我考虑过的一些解决方案:

• 临时表(感觉这对于这个用例来说太重量级了)。
• 使用 AFTER UPDATE 触发器(与事务显然尚未提交的问题相同)。

我正在使用 Postgres 9.4.1。

Answer 1

您可以创建延迟的唯一约束，在这种情况下，它将在您提交您的交易时检查，而不是在执行插入时检查:

CREATE TABLE fns
(
  id serial,
  start_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_date timestamptz,
  name text NOT NULL,
  parent_id integer,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES fns(id),
  UNIQUE(name) deferrable initially deferred --<< here
);