Re: 8.3.5 broken after power fail

"Achilleas Mantzios" <achill(at)matrix(dot)gatewaynet(dot)com> schrieb:
>> That runs now since 33 CPU minutes, using 100% with no visible I/O
>> happening. top says:
>> 29687 postgres 20 0 605m 187m 3024 R 100 15.4 32:58.65 postmaster
>>
>> And "strace -p 29687" displays nothing. Could it be the process hangs? It
>> shouldn't take that long I guess, but I'm not sure how to find out if
it's
>> borked or really working... ideas?
>
> iostat systat vmstat report anything?

Sorry, I wasn't detailed enough. With "no visible I/O" I meant I looked with
iostat.

After 45 CPU minutes I tried to stop it - no chance.
Stop postgres - no chance. Wow, a really hard problem.
Had to reboot the VM.

Can I somehow dump that table without using an index?

Here's the info about \d dbmail_messageblks
Tabelle »public.dbmail_messageblks«
Spalte | Typ | Attribute
-----------------+----------+------------------------------------------------------------------
messageblk_idnr | bigint | not null default
nextval('dbmail_messageblk_idnr_seq'::regclass)
physmessage_id | bigint |
messageblk | bytea | not null
blocksize | bigint | not null default (0)::bigint
is_header | smallint | not null default (0)::smallint
Indexe:
»dbmail_messageblks_pkey« PRIMARY KEY, btree (messageblk_idnr)
»dbmail_messageblks_1« UNIQUE, btree (physmessage_id, messageblk_idnr)
CLUSTER
»dbmail_messageblks_physmessage_is_header_idx« btree (physmessage_id,
is_header)
Fremdschlüssel-Constraints:
»dbmail_messageblks_physmessage_id_fkey« FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE

This works:
COPY public.dbmail_messageblks (messageblk_idnr, physmessage_id, blocksize,
is_header) TO stdout;
This does NOT work:
COPY public.dbmail_messageblks (messageblk_idnr, physmessage_id, messageblk,
blocksize, is_header) TO stdout;

So I'm sure the "messageblk" toast got toasted too much. Now I need a way to
get out all values that are readable and piss on the rest. I looked into the
output of the first and second COPY, it should be messageblk_idnr=3904492
that's broken. I tried
select * from dbmail_messageblks where messageblk_idnr not in (3904492);

That helps. Slowly I may be able to find all broken entries. Is there a more
automated way?

mfg zmi