Re: major database breakdown - Mailing list pgsql-general

From Tom Lane
Subject Re: major database breakdown
Date
Msg-id 4138.1092753009@sss.pgh.pa.us
Whole thread Raw
In response to Re: major database breakdown  (Ulrich Wisser <ulrich.wisser@relevanttraffic.se>)
List pgsql-general
Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:
> I did delete everything from pg_statistic. It helped somewhat but
> still there is something strange. Please see the transcript of my
> session below. How can I fix these broken indexes? I can't drop and
> recreate them.

I am not sure what's going on with pg_statistic, but it seems like
maybe there are some tuples that aren't getting deleted.  Try
    delete from pg_statistic
    vacuum verbose pg_statistic
(In a standalone backend it seems you need "set server_min_messages = debug"
to see anything from the vacuum verbose.)  If that shows a nonzero
number of remaining tuples then try "truncate pg_statistic" instead.
(I think you'll need to have started the backend with -O to be allowed
to do this.  Also it might be interesting to look at the remaining rows
with pg_filedump before you truncate.)

I suspect that the ultimate answer may be "dump and reload the
database" :-(.  If there are undeletable rows in pg_statistic, the only
explanation I can think of is transaction ID corruption (ie, their xmax
values are in the future) and the problem may affect other data too.

            regards, tom lane

pgsql-general by date:

Previous
From: David Rysdam
Date:
Subject: Re: Any recommended forums/wiki/blog s/w that uses
Next
From: Robby Russell
Date:
Subject: Re: Any recommended forums/wiki/blog s/w that uses