X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BB6089DCA01 for ; Wed, 18 Jan 2006 19:44:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75866-01 for ; Wed, 18 Jan 2006 19:44:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 2310A9DC820 for ; Wed, 18 Jan 2006 19:44:37 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 425373982E; Wed, 18 Jan 2006 17:44:40 -0600 (CST) Date: Wed, 18 Jan 2006 17:44:40 -0600 From: "Jim C. Nasby" To: Michael Crozier Cc: pgsql-performance@postgresql.org Subject: Re: Autovacuum / full vacuum (off-topic?) Message-ID: <20060118234440.GE17896@pervasive.com> References: <200601181115.51850.crozierm@conducivetech.com> <20060118225257.GY17896@pervasive.com> <200601181536.04758.crozierm@conducivetech.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200601181536.04758.crozierm@conducivetech.com> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.095 required=5 tests=[AWL=0.095] X-Spam-Score: 0.095 X-Spam-Level: X-Archive-Number: 200601/322 X-Sequence-Number: 16800 On Wed, Jan 18, 2006 at 03:36:04PM -0800, Michael Crozier wrote: > > On Wednesday 18 January 2006 14:52 pm, Jim C. Nasby wrote: > > I think it's a good idea, but you should take a look at the recently > > added functionality that allows you to investigate the contests of the > > FSM via a user function (this is either in 8.1 or in HEAD; I can't > > remember which). > > I will look at this when time allows. Perhaps there is a combination of > triggers on stat tables and asynchronous notifications that would provide > this functionality without getting too deep into the vacuum's transaction > logic? You can't put triggers on system tables, at least not ones that will be triggered by system operations themselves, because the backend bypasses normal access methods. Also, most of the really interesting info isn't logged anywhere in a system table; stuff like the amount of dead space, tuples removed, etc. > Were it too integrated with the vacuum, it would likely be too much for > contrib/, I assume. Probably. A good alternative might be allowing vacuum to output some machine-friendly information (maybe into a backend-writable file?) and then have code that could load that into a table (though presumably that could should be as simple as just a COPY). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461