On 12/4/15 11:34 AM, Gerdan Rezende dos Santos wrote:
Someone has some way of identifying all invalid blocks of a table postgresql? Plpgsql a function, a tool, somehow. I found one solution on http://www.postgresql.org/message-id/1184245756.24101.178.camel@xxxxxxxxxxxxxxxxxxxxxx, but I can not change in order to identify any defective blocks at once.
If your question is "How can I modify that function to report ALL invalid CTIDs?" then you probably need to use a cursor and wrap the FETCH in a BEGIN/END block with an exception handler. Something like:
DECLARE curs refcursor; rec record; last_good tid; bad boolean := false; BEGIN OPEN curs NO SCROLL FOR EXECUTE 'SELECT ctid FROM ' || table_name; LOOP BEGIN FETCH curs INTO rec; EXIT WHEN NOT FOUND; IF bad THEN RAISE WARNING 'Next good CTID %', rec.ctid; bad := false; END IF; last_good := rec.ctid; EXCEPTION WHEN OTHERS RAISE WARNING E'Error %: %\nLast good CTID %', SQLSTATE, SQLERRM, last_good; bad := true; END; END LOOP; END; -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
