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