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 ?