Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477

[Maris Jansons <maris@xxxxxxxxxx>]

I have identified the problematic column, in my case it is api_response, my version of script:

DO $f$
DECLARE
    baddata TEXT;
    badid INT;
BEGIN
FOR badid IN SELECT id FROM api_logs LOOP
    BEGIN
        SELECT api_response
        INTO baddata
        FROM api_logs where id = badid;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Data for ID % is corrupt', badid;
            CONTINUE;
    END;
END LOOP;
END;
$f$

However this script finishes without errors, but if I run simple

> select api_response from api_logs;

I get:

ERROR:  missing chunk number 0 for toast value 413511 in pg_toast_25477

Maybe I have messed something up in the script?

Best Regards,

Maris

---

From: "Jorge Torralba" <jorge.torralba@xxxxxxxxx>
To: "Maris Jansons" <maris@xxxxxxxxxx>
Cc: "pgsql-admin" <pgsql-admin@xxxxxxxxxxxxxx>
Sent: Wednesday, January 25, 2017 11:52:08 PM
Subject: Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477

I have run this with success several times.  I modified it to be intuitive. There may be a typo or two but have a look at this:

DO $f$

DECLARE

    baddata TEXT;

    badid INT;

BEGIN

FOR badid IN SELECT id FROM badtable LOOP

    BEGIN

        SELECT badcolumn

        INTO columndata

        FROM badtable where id = badid;

    EXCEPTION

        WHEN OTHERS THEN

            RAISE NOTICE 'Data for ID % is corrupt', badid;

            CONTINUE;

    END;

END LOOP;

END;

$f$