Re: Issue in pg_catalog.pg_indexes view definition

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