Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? - Mailing list pgsql-sql

Hi codeWarrior,

codeWarrior wrote:
> > For user_ids that have more than 50 rows, I want to keep the most
> > recent 50 and delete the rest.
> How about using a trigger to call a stored procedure ? [ON INSERT to
> user_item_history DO ...]

[snip]

Thanks for your input!  I've implemented this but I'm concerned about
performance.  As I mentioned it's frequently being added to and this
function will be called maybe a couple of times a second.  In my brief
initial tests it seems like this is running quite slowly...

Just to make sure I haven't done anything obviously wrong, I've
included my implementation below.. if you could look over it I'd
really appreciate it.

CREATE OR REPLACE FUNCTION user_item_history_limit() RETURNS TRIGGER   AS $_$
DECLARE   threshold integer = 50;   numrows integer;
BEGIN   SELECT INTO numrows count(*) FROM user_item_history WHERE user_id
= new.user_id;   IF numrows > threshold THEN       DELETE FROM user_item_history WHERE user_id = new.user_id AND
id NOT IN (           SELECT id FROM user_item_history           WHERE user_id = new.user_id           ORDER BY
timestampDESC LIMIT threshold);       RAISE NOTICE '% rows exceeds threshold of % for user_id %;
 
trimming..', numrows, threshold, new.user_id;   END IF;   RETURN new;
END;
$_$   LANGUAGE plpgsql;

CREATE TRIGGER user_item_history_limit AFTER INSERT ON user_item_history   FOR EACH ROW EXECUTE PROCEDURE
user_item_history_limit();

Any suggestions greatly appreciated!

Thanks again,
Jamie