Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 51FF32E45D1 for ; Tue, 8 Jan 2008 12:30:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 60055-01-5 for ; Tue, 8 Jan 2008 12:30:46 -0400 (AST) Received: from smtp146.iad.emailsrvr.com (smtp146.iad.emailsrvr.com [207.97.245.146]) by postgresql.org (Postfix) with ESMTP id 7A3C02E36EB for ; Tue, 8 Jan 2008 12:30:34 -0400 (AST) Received: from relay4.r5.iad.mlsrvr.com (localhost [127.0.0.1]) by relay4.r5.iad.mlsrvr.com (SMTP Server) with ESMTP id B865BC9E4; Tue, 8 Jan 2008 11:30:33 -0500 (EST) Received: by relay4.r5.iad.mlsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id 32208C9F9; Tue, 8 Jan 2008 11:30:29 -0500 (EST) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Date: Tue, 8 Jan 2008 10:30:28 -0600 To: Jamie Tufnell X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/46 X-Sequence-Number: 29899 On Jan 8, 2008, at 8:24 AM, Jamie Tufnell wrote: > 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 <=3D 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. Create an row trigger that fires after insert containing something =20 along the lines of : DELETE FROM user_item_history WHERE id IN (SELECT id FROM user_item_history WHERE user_id=3DNEW.user_id ORDER BY timestamp DESC OFFSET 50); =09 Erik Jones DBA | Emma=AE erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com