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 <postg...@arcict.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" <postg...@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 > >
