PostgreSQL 9.3: Primary key violated by a trigger INSERT

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"


• 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?

• Multiple single-value inserts in one transaction, or a single multi-valued insert statement? – Craig Ringer Aug 7 '14 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? – Erwin Brandstetter Aug 7 '14 at 4:01
• @ErwinBrandstetter Thanks! Updated my question accordingly. – Adam Matan Aug 7 '14 at 5:28

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.

• +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 '14 at 5:20
• @AdamMatan: Note, RETURNING was missing in my answer, added now. – Erwin Brandstetter Aug 7 '14 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. – ypercubeᵀᴹ Aug 7 '14 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 '14 at 15:12