PL/pgSQL question about EXCEPTION clause & corrupt records
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. 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
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 FROM f_gsxws_transaction where gwta_number = 762513 ORDER BY LOOP RAISE NOTICE 'examining row with = %',vid; select * into rcontent FROM f_gsxws_transaction where = vid; RAISE NOTICE 'content of row = % , 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
Re: PL/pgSQL question about EXCEPTION clause & corrupt records
"Nick Renders" 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
Re: PL/pgSQL question about EXCEPTION clause & corrupt records
> On Feb 14, 2020, at 04:39, Nick Renders wrote: > > I get the following message: > > ERROR: missing chunk number 0 for toast value 8289525 in pg_toast_5572299 What version of PostgreSQL are you running? I’ve seen this a number of times the past couple years; curious if the lurking bug is still observed in latest versions. -Jeremy Sent from my TI-83
