Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B02D82E3900 for ; Tue, 8 Jan 2008 13:51:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 59574-08-2 for ; Tue, 8 Jan 2008 13:51:15 -0400 (AST) Received: from starfish.geekisp.com (mail.geekisp.com [216.168.135.169]) by postgresql.org (Postfix) with ESMTP id 752B92E3A02 for ; Tue, 8 Jan 2008 13:51:16 -0400 (AST) Received: (qmail 16243 invoked by uid 1003); 8 Jan 2008 17:51:19 -0000 Received: from mobile-032-160-082-188.mycingular.net (HELO dino-1.misuse.org) (public@misuse.org@32.160.82.188) by mail.geekisp.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 8 Jan 2008 17:51:19 -0000 X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Tue, 08 Jan 2008 09:31:35 -0800 To: pgsql-sql@postgresql.org From: Steve Midgley Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? In-Reply-To: <20080108143128.DEA3C2E42A4@postgresql.org> References: <20080108143128.DEA3C2E42A4@postgresql.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_328364578==.ALT" Message-Id: <20080108175116.752B92E3A02@postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/50 X-Sequence-Number: 29903 --=====================_328364578==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed I think what you want is related to this post on how to create a FIFO queue in Postgres: http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html The major difference is that you want a FIFO queue per user_id, so the triggering code would want to bump old records aggregating on user_id to calculate the "limit" subquery. His original code is this: DELETE FROM q WHERE id NOT IN (SELECT id FROM q ORDER BY id DESC LIMIT maxrows); And that subquery is where (I think!) you'd want to add "where user_id = [user_id]" - I'm not sure how you'll pass user_id into this function, maybe someone else can help with that? Hopefully this is useful? Steve At 06:24 AM 1/8/2008, pgsql-sql-owner@postgresql.org wrote: >Date: Tue, 8 Jan 2008 14:24:22 +0000 >From: "Jamie Tufnell" >To: pgsql-sql@postgresql.org >Subject: How to keep at-most N rows per group? periodic DELETEs or >constraints or..? >Message-ID: > > >Hi, > >I have a table that stores per-user histories of recently viewed items >and I'd like to limit the amount of history items to <= 50 per user. >I'm considering doing this with a query run from cron every so often >but I'm not happy with what I've come up with so far, and since it's a >quite active table I thought I'd ask here to see if there's a more >efficient way. > >Right now the table structure is as follows... > >user_item_history: id (PK), user_id (FK), item_id (FK), timestamp > >For user_ids that have more than 50 rows, I want to keep the most >recent 50 and delete the rest. > >The most obvious way of doing this for me is: > >-- >-- Get the user_ids with 50 or more history entries like this >-- >SELECT user_id, count(*) >FROM user_scene_history >GROUP BY user_id >HAVING count(*) > 50; > >-- >-- Then iterate the ids above (_user_id) >-- >DELETE FROM user_scene_history >WHERE user_id = _user_id AND id NOT IN ( > SELECT id FROM user_scene_history > WHERE user_id = _user_id > ORDER BY timestamp DESC > LIMIT 50); > >I've left out the simple logic tying the above two queries together >for clarity.. > >I haven't actually tested this but while I assume it would work I >imagine there is a neater and possibly more efficient way of attacking >this. I'm also open to different approaches of limiting the user's >history too ... perhaps with table constraints so they can simply >never exceed 50 entries? But I'm not sure how to do this.. > >Any help would be greatly appreciated.. > >Thanks, >Jamie --=====================_328364578==.ALT Content-Type: text/html; charset="us-ascii" I think what you want is related to this post on how to create a FIFO queue in Postgres:

http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

The major difference is that you want a FIFO queue per user_id, so the triggering code would want to bump old records aggregating on user_id to calculate the "limit" subquery. His original code is this:


 DELETE FROM q WHERE id NOT IN
    (SELECT id FROM q ORDER BY id DESC LIMIT maxrows);
And that subquery is where (I think!) you'd want to add "where user_id = [user_id]" - I'm not sure how you'll pass user_id into this function, maybe someone else can help with that?

Hopefully this is useful?

Steve

At 06:24 AM 1/8/2008, pgsql-sql-owner@postgresql.org wrote:
Date: Tue, 8 Jan 2008 14:24:22 +0000
From: "Jamie Tufnell" <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Message-ID: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>

Hi,

I have a table that stores per-user histories of recently viewed items
and I'd like to limit the amount of history items to <= 50 per user.
I'm considering doing this with a query run from cron every so often
but I'm not happy with what I've come up with so far, and since it's a
quite active table I thought I'd ask here to see if there's a more
efficient way.

Right now the table structure is as follows...

user_item_history: id (PK), user_id (FK), item_id (FK), timestamp

For user_ids that have more than 50 rows, I want to keep the most
recent 50 and delete the rest.

The most obvious way of doing this for me is:

--
-- Get the user_ids with 50 or more history entries like this
--
SELECT user_id, count(*)
FROM user_scene_history
GROUP BY user_id
HAVING count(*) > 50;

--
-- Then iterate the ids above (_user_id)
--
DELETE FROM user_scene_history
WHERE user_id = _user_id AND id NOT IN (
    SELECT id FROM user_scene_history
    WHERE user_id = _user_id
    ORDER BY timestamp DESC
    LIMIT 50);

I've left out the simple logic tying the above two queries together
for clarity..

I haven't actually tested this but while I assume it would work I
imagine there is a neater and possibly more efficient way of attacking
this.  I'm also open to different approaches of limiting the user's
history too ... perhaps with table constraints so they can simply
never exceed 50 entries? But I'm not sure how to do this..

Any help would be greatly appreciated..

Thanks,
Jamie
--=====================_328364578==.ALT--