PostgreSQL 9.3: Primary key violated by a trigger INSERT

Question

My problem

Consider a table t with many frequent updates from users, from which only the last few are relevant.

In order to keep the table size reasonable, whenever a new row is inserted old rows from the same user_id are deleted. In order to keep an archive, the row is also written to t_history.

Both t and t_history have the same schema, in which id is a bigserial with a primary key constraint.

Implementation

Stored procedure

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;

Corresponding insertion trigger:

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

The error

The trigger works well, but when I run a few dozen insertions in a single transaction, I get the following error:

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

Updates

• The id field in both tables (from \d+ t):
  • id|bigint|not null default nextval('t_id_seq'::regclass)
  • "t_pkey" PRIMARY KEY, btree (id)
• PostgreSQL version is 9.3.

Any idea why the stored procedure breaks the primary key constraint in transactions?

Answer 1

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows.

I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem).

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;

The new row is already visible in an AFTER trigger.