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