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

FWIW, Bertrand blogged an even faster way to do this about a month ago - using pageinspect and processing blocks instead of rows

https://bdrouvot.wordpress.com/2020/01/18/retrieve-postgresql-variable-length-storage-information-thanks-to-pageinspect/

-J

Sent from my TI-83

> On Feb 17, 2020, at 03:32, Nick Renders <postgres(at)arcict(dot)com> wrote:
>
> 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
>
>