Обсуждение: missing chunk number 0 for toast value ...

Hello postgres wizards :-)

I have a problem with table info:

select * from info;
ERROR:  missing chunk number 0 for toast value 190937

Dumping does not work either:

pg_dump db >db.unl
pg_dump: ERROR:  missing chunk number 0 for toast value 190937
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "info" failed: PQendcopy()
failed.
pg_dump: Error message from server: pg_dump: The command was: COPY public.info
(idsection, idinfo, txt) TO stdout;

I googled, and using advice in an archived post I run the following command:
 select relname from pg_class where oid =
 (select reltoastrelid from pg_class where relname = 'info');

which resulted in one row with value pg_toast_61934

and then I ran

reindex table pg_toast.pg_toast_61934;

but it did not help.

What should I do to salvage the table?

--
Milos Prudek

MIlos Prudek <prudek@bvx.cz> writes:
> select * from info;
> ERROR:  missing chunk number 0 for toast value 190937

I was going to suggest the REINDEX, but no luck :-(

> What should I do to salvage the table?

Find the row containing the busted toast reference, and delete that row
(or just UPDATE it with some new value for the toasted column).  You can
home in on it with the usual sorts of search methods --- look in the PG
list archives for previous discussions of locating corrupted rows.

            regards, tom lane

> Find the row containing the busted toast reference, and delete that row
> (or just UPDATE it with some new value for the toasted column).  You can
> home in on it with the usual sorts of search methods --- look in the PG
> list archives for previous discussions of locating corrupted rows.

Thanks Tom. I ended up restoring the table from the last dump (created by
pg_dump) up to the point where corruption starts. Not the most surgical
solution but justified for my use case.


--
Milos Prudek