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(at)arcict(dot)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