Re: major database breakdown

Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)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