DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=NF7a57fPep/viqwXD23nuDa9jTS8XBg0soT3rO/hctRJyo6b+O5dTIBEpBo0dSK/M+olPmMAa7UWhU8S/mlrV3aoAzkElOS+5YKMrgf2QJj7+mVXX4/Olk1zPXjgUiuAIxn9cuIEvKmyZyChUvt8EqwZBxUWoq/Nx3AvOfpMmv4=
Message-ID: <b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com>
Date: Tue, 8 Jan 2008 16:31:08 +0000
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..?
In-Reply-To: <b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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