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, [EMAIL PROTECTED] wrote:
Date: Tue, 8 Jan 2008 14:24:22 +0000
From: "Jamie Tufnell" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: How to keep at-most N rows per group? periodic DELETEs or
constraints or..?
Message-ID:
<[EMAIL PROTECTED]>
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
