Re: pg_depend OBJID not found

On Wed, Jan 15, 2014 at 8:37 PM, saggarwal <sanj(dot)aggarwal(at)gmail(dot)com> wrote:
> <http://postgresql.1045698.n5.nabble.com/file/n5787214/ScreenShot.jpg> hi
>
> Any help appreciated (newbie to pgsql)
> I have a function f_Sanjeev and create a view
> create view v_sanjeev as select * from f_sanjeev()
>
> the view has and OBJID of 5134719
>
> oid reltype relname relnamespace reltype relowner relam relfilenode
> reltablespace relpages reltuples reltoastrelid reltoastidxid relhasindex
> relisshared relkind relnatts relexternid relisreplicated relispinned
> reldiststyle relprojbaseid relchecks reltriggers relukeys relfkeys relrefs
> relhasoids relhaspkey relhasrules relhassubclass relacl
> 5134719 5134720 v_sanjeev 4497152 5134720 104 0 5134719 0 0 0.0010 0 0 false
> false v 1 0 false false 0 0 0 0 0 0 0 false false true false (null)
>
> when I then check what dependencies there are on the Function f_Sanjeev
> using
> select * from pg_depend where refobjid = (select oid from pg_proc where
> proname='f_sanjeev');
>
> I get the following from pg_Depend
> classid objid objsubid refclassid refobjid refobjsubid deptype
> 16412 5134721 0 1255 4497477 0 n
>
> the OBJID is 5134721 which I cannot find anywhere. This number is always 1
> more than the ID in the pg_class.
>
> So I search pg_class oid=5134720 the view v_Sanjeev is found but if I search
> the OID=5134721 nothing is found
>
> this may be a known issue or I am missing a link somewhere
>
> any help greatly appreciated
>
> thanks
>
>

There would be "pg_rewrite" in between.

A possibly sloppy way you could get to the view is:

select c.*

from pg_class c, pg_rewrite rw, pg_depend d,
pg_proc p
where c.oid = rw.ev_class and
rw.oid = d.objid and
d.refobjid = p.oid and
p.proname = 'f_sanjeev';

here,

d.objid => oid of the rewrite rule
d.refobjid => oid of the function

So, the referencing object for 'f_sanjeev' is really a "rewrite rule"
(and not the view directly).

"pg_rewrite.ev_class" is the oid of the table that a given rewrite
rule is for which in this case is the view 'v_sanjeev'.

--
Amit