From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Date: Tue, 8 Jan 2008 16:31:08 +0000
Message-ID: <b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com> (raw)
In-Reply-To: <b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
References: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
<fm028v$106a$1@news.hub.org>
<b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
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 timestamp DESC 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
In-Reply-To: <b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox