Re: unable to dump database, toast errors

Lonni J Friedman wrote:
>
> On Mon, 7 Apr 2003, Manfred Koizar wrote:
> > On Mon, 07 Apr 2003 07:34:47 -0400, Jan Wieck <JanWieck(at)Yahoo(dot)com>
> > wrote:
>
> [...]
>
> # select ctid from artifact_file LIMIT 1 OFFSET 694;
> ctid
> ---------
> (16,33)
>
> # select LENGTH(bin_data) from artifact_file where ctid='(16,33)';
> ERROR: missing chunk number 1 for toast value 7685119
>
> # select sum(length(bin_data)) from artifact_file where NOT
> ctid='(16,33)';
> sum
> -----------
> 293963428
>
> So, where do i go from here? Is it now just a matter of nullifying
> bin_data for that particular row, or is it more involved? thanks.

I think an

UPDATE artifact_file SET bin_data = '' WHERE ctid = '(16,33)';

should make a

SELECT sum(length(bin_data)) FROM artifact_file;

work again. If that is the case, dump the whole database system with
pg_dumpall. If and only if that is okay too, save the current
pg_hba.conf and postgres.conf files, remove the whole PGDATA directory,
run initdb, restore the dump and finally restore the pg_hba.conf and
postgres.conf files.

Another question is how did it come to the corrupted TOAST table. Have
you run test programs that check for bad memory modules? Have you
checked your harddisks for badblocks? Are you running an overclocked
system? So basically, can you trust your hardware as far as you can
throw it?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #