X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B5DB89FA1E2 for ; Tue, 12 Sep 2006 23:24:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 51709-09 for ; Tue, 12 Sep 2006 23:24:52 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 0895A9FA15C for ; Tue, 12 Sep 2006 23:24:51 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k8D2Og9D078168 for ; Tue, 12 Sep 2006 23:24:42 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k8D2KwQR077599 for pgsql-admin@postgresql.org; Tue, 12 Sep 2006 23:20:58 -0300 (ADT) (envelope-from news) From: andy X-Newsgroups: pgsql.admin Subject: Re: Vacuum error on database postgres Date: Tue, 12 Sep 2006 21:20:55 -0500 Organization: Hub.Org Networking Services Lines: 63 Message-ID: References: <44F82150.5000809@pnlassociates.com> <4001.1157121669@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) In-Reply-To: <4001.1157121669@sss.pgh.pa.us> To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/123 X-Sequence-Number: 23037 Tom Lane wrote: > "Paul B. Anderson" writes: >> I did delete exactly one of each of these using ctid and the query then >> shows no duplicates. But, the problem comes right back in the next >> database-wide vacuum. > > That's pretty odd --- I'm inclined to suspect index corruption. > >> I also tried reindexing the table. > > Get rid of the duplicates (actually, I'd just blow away all the > pg_statistic entries for each of these tables) and *then* reindex. > Then re-analyze and see what happens. > > Worst case you could just delete everything in pg_statistic, reindex it, > do a database-wide ANALYZE to repopulate it. By definition there's not > any original data in that table... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > Hello, newbe here.. I seem to have run across this same error. And I believe I can re-create it too. I'm running 8.1.4 on slackware-(almost 11). I have two scripts, one create's tables and indexes, the other has lots of "copy from stdin" statements. The very last line is VACUUM VERBOSE ANALYZE; which eventually gives me the same error. This is a test box, with test data, and this seemed to work: delete from pg_statistic; reindex table pg_statistic; vacuum analyze; So I'm ok, but I tried it again, by dropping the database and re-running both scripts and got the same error again. So thought I'd offer a test case if there was interest. The data is 46 meg compressed and 500'ish meg uncompressed. Its a little bit sensitive, so I'd like to be a little discreet with it, but can put it on a website for a developer to download. Please contact me privately for a link: andy@squeakycode.net ...Oh, also, this box has locked up on me a few times, so its not the most stable thing in the world. It did not lock up during import of this data, but there is flaky hardware in there someplace, so it could very well be a hardware issue. -Andy