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 C8D369FB525 for ; Fri, 1 Sep 2006 13:06:26 -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 01683-03-2 for ; Fri, 1 Sep 2006 13:04:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail711.megamailservers.com (mail711.megamailservers.com [69.49.98.21]) by postgresql.org (Postfix) with ESMTP id E6B559FB618 for ; Fri, 1 Sep 2006 12:04:49 -0300 (ADT) X-Authenticated-User: paul.a.pnlassociates.com Received: from [69.3.2.106] (h-69-3-2-106.mclnva23.covad.net [69.3.2.106]) (authenticated bits=0) by mail711.megamailservers.com (8.13.6/8.13.1) with ESMTP id k81F4aZF005401 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Fri, 1 Sep 2006 11:04:45 -0400 Message-ID: <44F84C04.1030805@pnlassociates.com> Date: Fri, 01 Sep 2006 11:04:36 -0400 From: "Paul B. Anderson" User-Agent: Thunderbird 1.5.0.5 (Macintosh/20060719) MIME-Version: 1.0 To: Tom Lane CC: "[ADMIN]" Subject: Re: Vacuum error on database postgres References: <44F82150.5000809@pnlassociates.com> <4001.1157121669@sss.pgh.pa.us> In-Reply-To: <4001.1157121669@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050305080308030106070700" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.38 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/6 X-Sequence-Number: 22920 This is a multi-part message in MIME format. --------------050305080308030106070700 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I removed the duplicates and then immediately reindexed. All is well. The vacuum analyze on the postgres database works now too. Thanks. It is good to know the pg_statistic table can be emptied in case this ever happens again. Paul 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 > > . > > --------------050305080308030106070700 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I removed the duplicates and then immediately reindexed.  All is well.  The vacuum analyze on the postgres database works now too.  Thanks.

It is good to know the pg_statistic table can be emptied in case this ever happens again.

Paul

Tom Lane wrote:
"Paul B. Anderson" <paul.a@pnlassociates.com> 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

.

  
--------------050305080308030106070700--