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

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 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.
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 corrupt records in a database?
>
> Best regards,
>
> Nick Renders
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt