From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Date: Tue, 08 Jan 2008 09:31:35 -0800
Message-ID: <20080108175116.752B92E3A02@postgresql.org> (raw)
In-Reply-To: <20080108143128.DEA3C2E42A4@postgresql.org>
References: <20080108143128.DEA3C2E42A4@postgresql.org>
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-...
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
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: public@misuse.org
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
In-Reply-To: <20080108175116.752B92E3A02@postgresql.org>
* 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