主页 / dba / 问题 / 73334
Accepted
Adam Matan
Asked: 2014-08-07 09:13:53 +0800 CST

PostgreSQL 9.3：触发器 INSERT 违反了主键

  • 772

我的问题

考虑一个t包含许多来自用户的频繁更新的表，其中只有最后几个是相关的。

为了保持表的大小合理，每当插入新行时，旧行都会user_id被删除。为了保留存档，该行也被写入t_history.

t和都有t_history相同的模式，其中id有bigserial一个主键约束。

执行

存储过程

CREATE FUNCTION update_t_history()
RETURNS trigger
AS
$$
declare
BEGIN
    -- Insert the row to the t_history table. `id` is autoincremented
    INSERT INTO t_history (a, b, c, ...)
    VALUES (NEW.a, NEW.b, NEW.c, ...);

    -- Delete old rows from the t table, keep the newest 10 
    DELETE FROM t WHERE id IN (
                  SELECT id FROM t 
                  WHERE user_id = NEW.user_id 
                  ORDER BY id DESC
                  OFFSET 9);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

对应的插入触发器：

CREATE TRIGGER t_insertion_trigger
AFTER INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE update_t_history();

错误

触发器运行良好，但是当我在单个事务中运行几十个插入时，出现以下错误：

BEGIN
ERROR:  duplicate key value violates unique constraint "t_history_pkey"
DETAIL:  Key (id)=(196) already exists.

更新

  • 两个表中的id字段（来自\d+ t）：
    • id|bigint|not null default nextval('t_id_seq'::regclass)
    • "t_pkey" PRIMARY KEY, btree (id)
  • PostgreSQL 版本为 9.3。

知道为什么存储过程会破坏事务中的主键约束吗？

stored-procedures postgresql

1 个回答

  1. Best Answer

    为什么首先要t_history.id自动递增？如果"both tand t_historyhave the same schema"并且t.id是串行 PK，则可以只复制整行。

    我还建议您只在数据修改 CTE 中将实际删除的行复制t到- 中。t_history这样你就没有重叠的行（这可能是问题的一部分）。

    CREATE FUNCTION update_t_history()
  RETURNS trigger AS
$func$
BEGIN
   -- Keep the newest 10, move older rows to t_history
   WITH del AS (
      DELETE FROM t
      USING (
         SELECT id
         FROM   t 
         WHERE  user_id = NEW.user_id 
         ORDER  BY id DESC
         OFFSET 10      -- to keep 10 (not 9)
         FOR UPDATE     -- avoid race condition
         ) d
      WHERE t.id = d.id
      RETURNING t.*
      )
   INSERT INTO t_history 
   SELECT * FROM del;   -- copy whole row

   RETURN NULL;         -- irrelevant in AFTER trigger
END
$func$  LANGUAGE plpgsql;

    新行已在AFTER触发器中可见。

    • 2

相关问题