Re: PL/pgSQL question about EXCEPTION clause & corrupt records

The problem is that I don't know which column is corrupt. But I found a 
solution: by simply copying the record into another variable, the values 
are parsed and the TOAST errors are thrown.

In case anyone's interested, here's my code, based on an example from 
http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html


DO $f$
DECLARE
    rContent1 record;
    rContent2 record;
    iCounter integer DEFAULT 1;
    iValue integer;
    pTableName varchar := 'f_gsxws_transaction';
    pFieldName varchar := 'gwta_number';
BEGIN
    FOR iValue IN EXECUTE 'SELECT ' || pFieldName || ' FROM ' || 
pTableName::regclass || ' ORDER BY ' || pFieldName LOOP
        BEGIN
            EXECUTE 'SELECT * FROM ' || pTableName::regclass || ' WHERE ' || 
pFieldName || ' = $1'
                INTO rContent1
                USING iValue;
            rContent2 := rContent1;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'data for %.% % is corrupt', pTableName, pFieldName, 
iValue;
        END;
        IF iCounter % 100000 = 0 THEN
            RAISE NOTICE '% % records checked', iCounter, pTableName;
        END IF;
        iCounter := iCounter+1;
    END LOOP;
END;
$f$;


Cheers,

Nick


On 14 Feb 2020, at 16:14, Tom Lane wrote:

> "Nick Renders" <postgres@arcict.com> writes:
>> I thought I would write a little PL script that would loop through 
>> all
>> the data and report any inconsistencies. However, I can't get it to 
>> work
>> properly.
>> ...
>> 1) The function has no problem executing the SELECT statement. It is
>> only when "rcontents" is returned, that the function fails. This is a
>> problem, because the ultimate goal is to loop through all records and
>> only return/alert something in case of an error.
>> 2) The function never enters the EXCEPTION clause. Instead, when it 
>> hits
>> the RETURN command, it breaks and shows the same message as in 
>> pgAdmin:
>> missing chunk number 0 for toast value 8289525 in pg_toast_5572299.
>
> I think what's happening there is that the function doesn't try to
> dereference the value's TOAST pointer during SELECT INTO.  It just 
> stores
> that pointer into a variable, and only sometime later when the actual
> content of the value is demanded, do you see the error raised.
>
> The solution to that is to do something that uses the contents of the
> busted column right away while still inside the EXCEPTION block, 
> perhaps
> along the lines of "select md5(mycolumn) into local_variable from..."
>
> A close reading of
>
> https://www.postgresql.org/docs/current/storage-toast.html
>
> would probably help you understand what's happening here.
>
>             regards, tom lane