Issue in pg_catalog.pg_indexes view definition

From: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Issue in pg_catalog.pg_indexes view definition
Date: 2016-07-14 07:08:32
Message-ID: CAFiTN-uwqqEeRJKZr4wk6qorZfCO_3LptLK-qwtXO+umLvw1uA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 ?

Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2016-07-14 07:18:37 Re: unexpected psql "feature"
Previous Message Craig Ringer 2016-07-14 06:35:29 Re: One process per session lack of sharing