1

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?

3
  • Multiple single-value inserts in one transaction, or a single multi-valued insert statement? Aug 7, 2014 at 0:50
  • Always your version of Postgres, please. Also, how exactly is t_history.id autoincremented? Best provide the table definition you get with \d tbl in psql. And asre you sure you are not copying t.id in the INSERT statement? Aug 7, 2014 at 4:01
  • @ErwinBrandstetter Thanks! Updated my question accordingly.
    – Adam Matan
    Aug 7, 2014 at 5:28

1 Answer 1

2

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.

4
  • +1 Thanks! I will try your solution and write you back. I don't see how can we have overlapping rows on INSERT, because we INSERT the same row to both tables once, and t never had overlap issues. BTW, I didn't know FOR UPDATE - will read about it.
    – Adam Matan
    Aug 7, 2014 at 5:20
  • @AdamMatan: Note, RETURNING was missing in my answer, added now. Aug 7, 2014 at 13:49
  • @AdamMatan if the insert is done on both tables, then any update will have to be done on both as well. The deletes of course will be trivial. Aug 7, 2014 at 13:56
  • I followed your advice, and simply added id and NEW.id to the INSERT statement in my code. It did the trick - I wonder why.
    – Adam Matan
    Aug 7, 2014 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.