On 14/2/20 2:39 μ.μ., Nick Renders wrote:
>
> Hello,
>
> We recently suffered a database crash which resulted in some corrupt records.
>
> 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.
>
> For instance, if I run the following statement in pgAdmin:
>
> SELECT * FROM f_gsxws_transaction WHERE gwta_number = 762513
>
> I get the following message:
>
> ERROR: missing chunk number 0 for toast value 8289525 in pg_toast_5572299
>
>
> So, as a test, I created a function that would just retrieve that one record:
>
> DECLARE
> rcontent f_gsxws_transaction%ROWTYPE;
> BEGIN
> SELECT * INTO rcontent FROM f_gsxws_transaction where gwta_number = 762513;
> RETURN rcontent;
> EXCEPTION WHEN OTHERS THEN
> RAISE NOTICE 'Record 762513 is corrupt';
> END;
>
>
> Now, when I run this function, I have noticed two things:
>
> 1) The function has no problem executing the SELECT statement. It is only when "rcontents" is returned, that the
functionfails. 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
samemessage as in pgAdmin: missing chunk number 0 for toast value 8289525 in
> pg_toast_5572299.
Does the table have any PKs or UKs?
do something like
FOR vid IN SELECT <somepkid> FROM f_gsxws_transaction where gwta_number = 762513 ORDER BY <somepkid> LOOP
RAISE NOTICE 'examining row with <somepkid>= %',vid;
select * into rcontent FROM f_gsxws_transaction where <somepkid> = vid;
RAISE NOTICE 'content of row <somepkid>= % , is % ',vid,rcontent;
END LOOP;
>
>
> Is it possible to check for these kind of errors with a PL script? Or is there perhaps a better way to check for
corruptrecords in a database?
>
> Best regards,
>
> Nick Renders
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
