Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

> ADSJ (Adam Sjøgren) wrote:
>
>> Our database has started reporting errors like this:
>>
>> 2017-05-31 13:48:10 CEST ERROR: unexpected chunk number 0 (expected 1) for toast value 14242189 in pg_toast_10919630

> Does the problem still reproduce if you revert commit
> 6c243f90ab6904f27fa990f1f3261e1d09a11853?

I will try and get back to you with the results (building new .deb
packages as I type this).

Meanwhile, I can report that I have upgraded from 9.3.14 to 9.3.17 and
the errors keep appearing the log.

We have tried running a function similar to the one described in
http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html as
suggested by Adrian Klaver, but we haven't been able to get any errors
from that.

This is the function we have run over our two tables:

CREATE OR REPLACE FUNCTION check_table_a(from_id int, to_id int)
RETURNS VOID LANGUAGE PLPGSQL AS
$f$
declare
curid INT := 0;
rec RECORD;
badid INT;
detoast TEXT;
begin
FOR badid IN SELECT id FROM table_a where id >= from_id and id <= to_id LOOP
curid = curid + 1;
if curid % 10000 = 0 then
raise notice '% rows inspected (%, %,%)', curid, badid, from_id, to_id;
end if;
begin
SELECT *
INTO rec
FROM table_a where id = badid;
detoast := substr(rec.fts::text,1,2000);
exception
when others then
raise notice 'data for table_a id: % is corrupt', badid;
continue;
end;
end loop;
end;
$f$;

-- The other function has:
--
detoast := substr(vcontent.document,1,2000);
--
-- and is otherwise identical.

But no 'data for table... is corrupt' is printed.

We are only substr()'ing one field (which we know is big) for each row.
Should we do so for _all_ fields? Is there an elegant way to do so?

Best regards,

Adam

--
"Lägg ditt liv i min hand Adam Sjøgren
Sälj din själ till ett band" adsj(at)novozymes(dot)com