Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9E8072E0085 for ; Wed, 9 Jan 2008 16:18:02 -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 31168-02-4 for ; Wed, 9 Jan 2008 16:17:49 -0400 (AST) Received: from smtp136.iad.emailsrvr.com (smtp136.iad.emailsrvr.com [207.97.245.136]) by postgresql.org (Postfix) with ESMTP id DD8CA2E2F51 for ; Wed, 9 Jan 2008 16:17:46 -0400 (AST) Received: from relay3.r3.iad.emailsrvr.com (localhost [127.0.0.1]) by relay3.r3.iad.emailsrvr.com (SMTP Server) with ESMTP id 16E4B44ED4E; Wed, 9 Jan 2008 15:17:46 -0500 (EST) Received: by relay3.r3.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id C766944ED45; Wed, 9 Jan 2008 15:17:45 -0500 (EST) In-Reply-To: References: <20080109152431.79AFF2E02F6@postgresql.org> <20080109182115.6E39C2E3239@postgresql.org> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: "Steve Midgley" , pgsql-sql-owner@postgresql.org, pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Date: Wed, 9 Jan 2008 14:17:39 -0600 To: Scott Marlowe X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/58 X-Sequence-Number: 29911 On Jan 9, 2008, at 1:09 PM, Scott Marlowe wrote: > On Jan 9, 2008 12:20 PM, Steve Midgley wrote: >> This is kludgy but you would have some kind of random number test at >> the start of the trigger - if it evals true once per every ten =20 >> calls to >> the trigger (say), you'd cut your delete statements execs by about =20= >> 10x >> and still periodically truncate every set of user rows fairly =20 >> often. On >> average you'd have ~55 rows per user, never less than 50 and a few >> outliers with 60 or 70 rows before they get trimmed back down to 50.. >> Seems more reliable than a cron job, and solves your problem of an =20= >> ever >> growing table? You could adjust the random number test easily if you >> change your mind of the balance of size of table vs. # of delete >> statements down the road. > > And, if you always through a limit 50 on the end of queries that > retrieve data, you could let it grow quite a bit more than 60 or 70... > Say 200. Then you could have it so that the random chopper function > only gets kicked off every 100th or so time. I like that idea. 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