>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Please note i have tried the chk function >> declare t text; >> begin t := $1;
>> return false; >> exception when others then return true;>> end;
>> which did not work.
Tom> You would get better responses if you defined what you meant byTom> "did not work", but I'm going to guess that
theissue is that thisTom> code failed to expose corrupted data. That's probably because itTom> would have just
assignedthe bad datum to "t" withoutTom> decompressing it.
The original intended use of that function (which is one I used to give
out regularly to people on IRC when working with them on data corruption
issues, and no doubt some of them have subsequently posted it on blogs
or whatnot) is to pass in the whole-row var for $1 like so:
select ctid, id from brokentable t where chk(t);
the intent being to detect failures of external toast fetches or other
corruption symptoms.
Unfortunately this is no longer as useful as it was, since a fix some
time back now has chk(t) detoast the fields of t before entering the
function, so the exception doesn't get caught. These days I usually
have people use this one instead:
create function chk(tid) returns boolean language plpgsql
as $f$ declare r text; begin r := (select t from brokentable t where ctid=$1); return false; exception when
othersthen return true; end;
$f$;
select ctid, id from brokentable t where chk(ctid);
In both versions of the function, it's relying on the fact that
everything will get detoasted and decompressed as part of casting the
record value to text.
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs