On 2016/07/14 16:08, Dilip Kumar wrote:
> 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"))*
> .......
...
> 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 ?
Can we say that pg_get_indexdef() has "side-effects" because it can error
like this? Shouldn't such a function be marked *volatile*? Because if I
do so by updating pg_proc, the plan changes (perhaps) to a safe one in
this context:
explain (costs off) select * from pg_catalog.pg_indexes where indexdef is
not null; QUERY PLAN
-----------------------------------------------------------------------------------------Subquery Scan on pg_indexes
Filter:(pg_indexes.indexdef IS NOT NULL) -> 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: (relkind =
'i'::"char") -> Hash -> Hash Join Hash
Cond:(x.indrelid = c.oid) -> Seq Scan on pg_index x ->
Hash -> Seq Scan on pg_class c Filter:
(relkind= ANY
('{r,m}'::"char"[])) -> Hash -> Seq Scan on pg_namespace n -> Materialize
-> Seq Scan on pg_tablespace t
(21 rows)
Thanks,
Amit