Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id F0FA42E0052 for ; Tue, 29 Apr 2008 00:31:27 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06414-10 for ; Tue, 29 Apr 2008 00:31:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by developer.postgresql.org (Postfix) with ESMTP id 424FC2E0051 for ; Tue, 29 Apr 2008 00:31:18 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n7so8537288wag.5 for ; Mon, 28 Apr 2008 20:31:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=5atHsLzD4dhJV4DW8FZsSVbeqpWNTrE85KpSxrMA7P8=; b=tZR96G4dAAYwP+ZGJNn9A+/XZTDOBReUaPS4nOjjbizKrPKoh12bX16NDlcgnTzg8E4jDZN0Rz51ENuzxt70JodT2+m7afWpsFkBEf9gaNPC/qge3F2RdH7rug54/DcGnNDFZD0aOOG+TkSFyYFGIkGCHqqO/+ejyZX9oZp0G14= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Gm90TRiGa3cHOeku6Gtkpehq73a3W5YpYSQ3dGFWnAK49cOpYuLAb0DNIERT62eGpDDeQrfBWRy5a0zSMNd4Dg2v8lhCr8o0/D1FQTHEGxy29xpkK/bHZJbSLOyyrsaOTSymL3tMdNjwF6GV7z2AiNQfh39JlBeaAv4RzlnJuZQ= Received: by 10.114.146.1 with SMTP id t1mr7393614wad.20.1209439877433; Mon, 28 Apr 2008 20:31:17 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Mon, 28 Apr 2008 20:31:17 -0700 (PDT) Message-ID: Date: Mon, 28 Apr 2008 21:31:17 -0600 From: "Scott Marlowe" To: "Gurjeet Singh" Subject: Re: Truncate table at a certain size. Cc: "Dana Huggard - Navarik" , pgsql-sql@postgresql.org In-Reply-To: <65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1209422104.12712.12.camel@H-S12.internal.navarik.com> <65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/220 X-Sequence-Number: 30759 On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh wrote: > On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik > wrote: > > > Hello, > > > > What would be the best method to truncate a table once it reaches a > > certain size. > > > > For instance, a table named log. I can check the size of the log; > > > > db=# select pg_relation_size('log'); > > pg_relation_size > > ------------------ > > 8192 > > (1 row) > > > > > > What I would like to do is > > > > If table log, is greater than xxxx bytes > > then truncate table log > > > > No, you wouldn't want to do this.... First and foremost, SQL (and Postgres) > does not guarantee that the new rows you are inserting land at some specific > point in storage; so you can't really be sure which part you want to > truncate. I think the OP was talking about running the truncate command on them... if select pg_relation_size('log') > somesize then truncate log; If that's the case he can either iterate a list of tables in plpgsql, an external scripting language, or write some select statement that creates truncates for all the tables over x size. something like: select 'tuncate '||relname||';' from (rest of query from psql -E and \d here) where pg_relation(relname) > somesize;