While running sqlsmith tool, I saw some cache lookup failure issues
While investigating those issues, I found one strange reason, and I feel
It's a bug in pg code.
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"))*
pg_get_indexdef(oid) clause is pushed down to pg_class, Which is logically
but pg_class will have other oids also (which are not index) and will get
I think problem is in definition of pg_indexes view,
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
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.
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 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
Is this a bug ?
If yes, what should be the right fix ?