Thread: "Cache lookup failed for relation 16905" ??
Hello all, I got a weird behavior testing PostgreSQL 7.3b3. The query below was run in a FreeBSD 4.4 system, on a fresh install and just I just had made an initb. No classes created at all. howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); ERROR: Cache lookup failed for relation 16905 ERROR: Cache lookup failed for relation 16905 What does that message mean ? there is not even such a relation in pg_class. Thanks. -- Best regards,Steve Howe mailto:howe@carcass.dhs.org
Steve Howe <howe@carcass.dhs.org> writes: > I got a weird behavior testing PostgreSQL 7.3b3. The query below was > run in a FreeBSD 4.4 system, on a fresh install and just I just had > made an initb. No classes created at all. > howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname > from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); > ERROR: Cache lookup failed for relation 16905 > ERROR: Cache lookup failed for relation 16905 I think you want pg_function_is_visible, not pg_table_is_visible. However this does show that the foo_is_visible functions aren't reacting very nicely when given bad input: they all put out a "Cache lookup failed" message, rather than something more easily interpretable. I'm not quite sure what should happen when foo_is_visible is called with an OID that is not the OID of any foo object; should it quietly return false, or should it raise a "no such object" error? regards, tom lane
Hello Tom, Saturday, November 2, 2002, 11:26:56 AM, you wrote: TL> Steve Howe <howe@carcass.dhs.org> writes: >> I got a weird behavior testing PostgreSQL 7.3b3. The query below was >> run in a FreeBSD 4.4 system, on a fresh install and just I just had >> made an initb. No classes created at all. >> howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname >> from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); >> ERROR: Cache lookup failed for relation 16905 >> ERROR: Cache lookup failed for relation 16905 TL> I think you want pg_function_is_visible, not pg_table_is_visible. Oh, you are totally right, I got distracted late in the work. I wanted pg_function_is_visible(). TL> However this does show that the foo_is_visible functions aren't reacting TL> very nicely when given bad input: they all put out a "Cache lookup TL> failed" message, rather than something more easily interpretable. Yes, it confused me, even if the mistake was mine. TL> I'm not quite sure what should happen when foo_is_visible is called with TL> an OID that is not the OID of any foo object; should it quietly return TL> false, or should it raise a "no such object" error? My opinion is that if should just return false... one can test if the object exists easily, if he/she needs it (WHERE p.oid in (select oid from pg_proc)). However, this situation lead me into another issue. The new conversion functions (utf8_to_big5, iso_to_alt, etc.) appear as listed in every new scheme. Is this correct ? Shouldn't them be listed only in pg_catalog? How can I distinguish user-defined functions from catalog (pre-defined) functions ? ------------- Best regards,Steve Howe mailto:howe@carcass.dhs.org
Steve Howe <howe@carcass.dhs.org> writes: > howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname > from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); > However, this situation lead me into another issue. The new conversion > functions (utf8_to_big5, iso_to_alt, etc.) appear as listed in every > new scheme. Is this correct ? Shouldn't them be listed only in > pg_catalog? They would be, if you had remembered the join condition ;-) where ... and p.pronamespace = n.oid regards, tom lane
Hello Tom, Saturday, November 2, 2002, 5:17:29 PM, you wrote: TL> Steve Howe <howe@carcass.dhs.org> writes: >> howe=# select p.oid, n.nspname, pg_get_userbyid(p.proowner), proname >> from pg_proc as p, pg_namespace as n where pg_table_is_visible(p.oid); >> However, this situation lead me into another issue. The new conversion >> functions (utf8_to_big5, iso_to_alt, etc.) appear as listed in every >> new scheme. Is this correct ? Shouldn't them be listed only in >> pg_catalog? TL> They would be, if you had remembered the join condition ;-) Oh, hehe. It worked, thank you. I remembered to include the condition on the other metadata queries, but forgot this one. Btw I wanted to thank you Tom and Bruce for the "HACKERS] 7.3 gotchas for applications and client libraries" post, it really helped a lot. And also for solving the issues on the returned info on INSTEAD rule queries... thanks. ------------- Best regards,Steve Howe mailto:howe@carcass.dhs.org