On 14/2/20 2:39 μ.μ., Nick Renders wrote:


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:

        rcontent f_gsxws_transaction%ROWTYPE;
        SELECT * INTO rcontent FROM f_gsxws_transaction where gwta_number = 
        RETURN rcontent;
        RAISE NOTICE 'Record 762513 is corrupt';

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;

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
Dynacom Tankers Mgmt

Reply via email to