pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: andy <andy@squeakycode.net>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum error on database postgres
Date: Tue, 12 Sep 2006 21:20:55 -0500
Message-ID: <ee7pua$2bou$1@news.hub.org> (raw)
In-Reply-To: <4001.1157121669@sss.pgh.pa.us>
References: <44F82150.5000809@pnlassociates.com>
	<4001.1157121669@sss.pgh.pa.us>

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
> 
> ---------------------------(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



view thread (21+ messages)  latest in thread

Message-ID: <ee7pua$2bou$1@news.hub.org>
Permalink:  ../ee7pua$2bou$1@news.hub.org/
Also on:    postgresql.org/message-id/ee7pua$2bou$1@news.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: andy@squeakycode.net
  Subject: Re: Vacuum error on database postgres
  In-Reply-To: <ee7pua$2bou$1@news.hub.org>
  References: <44F82150.5000809@pnlassociates.com> <4001.1157121669@sss.pgh.pa.us> <ee7pua$2bou$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox