PostgreSQL 9.3：觸發器 INSERT 違反了主鍵

我的問題

考慮一個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。

知道為什麼儲存過程會破壞事務中的主鍵約束嗎？

為什麼首先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觸發器中可見。

引用自：https://dba.stackexchange.com/questions/73334