While running sqlsmith tool, I saw some cache lookup failure issues reported,
While investigating those issues, I found one strange reason, and I feel It's a bug in pg code. Query: postgres=# select * from pg_catalog.pg_indexes where indexdef is not null; ERROR: cache lookup failed for index 2619 If we see the plan for the same: ------------------------------------------- Nested Loop Left Join Join Filter: (t.oid = i.reltablespace) -> Hash Left Join Hash Cond: (c.relnamespace = n.oid) -> Hash Join Hash Cond: (i.oid = x.indexrelid) * -> Seq Scan on pg_class i* * Filter: ((pg_get_indexdef(oid) IS NOT NULL) AND (relkind = 'i'::"char"))* ....... Problem Analysis: ------------------------- pg_get_indexdef(oid) clause is pushed down to pg_class, Which is logically correct, but pg_class will have other oids also (which are not index) and will get cache lookup failure error. I think problem is in definition of pg_indexes view, (projectio"*pg_get_indexdef(i.oid) AS indexdef*"). Basically we are using some function which can only be called on index oid otherwise we will get an error. So logically both view and push down in above query is fine, but we are using restricted function (*pg_get_indexdef(i.oid)*) which should not be push down. Or should be pushed down to pg_index. View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, *pg_get_indexdef(i.oid) *AS indexdef FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND i.relkind = 'i'::"char"; I am not sure what should be the correct fix for this problem. I think even if we try to call this function on index oid *pg_get_indexdef(* x.indexrelid*) *AS indexdef, problem will not be solved, because both will fall in same equivalence class hence clause can be distributed to pg_class also. Is this a bug ? If yes, what should be the right fix ? Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com