Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 14FB69FBB4A for ; Tue, 8 May 2007 07:51:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 63338-08 for ; Tue, 8 May 2007 07:51:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id 2F3AE9FBB4F for ; Tue, 8 May 2007 07:51:01 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1HlNHX-00011I-0F; Tue, 08 May 2007 11:50:59 +0100 From: Gregory Stark To: "Pomarede Nicolas" Cc: "Heikki Linnakangas" , Subject: Re: truncate a table instead of vaccum full when count(*) is 0 In-Reply-To: (Pomarede Nicolas's message of "Tue, 8 May 2007 12:23:56 +0200 (CEST)") Organization: EnterpriseDB References: <46404BEA.5060009@enterprisedb.com> X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 224) Date: Tue, 08 May 2007 11:50:58 +0100 Message-ID: <87sla7zizx.fsf@oxford.xeocode.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/106 X-Sequence-Number: 24496 "Pomarede Nicolas" writes: > Yes, I already do this on another spool table ; I run a vacuum after processing > it, but I wondered if there was another way to keep the disk size low for this > table. "after processing it" might be too soon if there are still transactions around that are a few minutes old and predate you committing after processing it. But any table that receives as many deletes or updates as these tables do will need to be vacuumed on the order of minutes, not days. >> It should work, just like you describe it, with the caveat that TRUNCATE will >> remove any old row versions that might still be visible to an older >> transaction running in serializable mode. > > Shouldn't locking the table prevent this ? I mean, if I try to get an exclusive > lock on the table, shouldn't I get one only when there's no older transaction, > and in that case I can truncate the table safely, knowing that no one is > accessing it due to the lock ? It would arise if the transaction starts before you take the lock but hasn't looked at the table yet. Then the lock table succeeds, you truncate it and commit, then the old transaction gets around to looking at the table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com