Re: pg_depend OBJID not found

On Wed, Jan 15, 2014 at 8:37 PM, saggarwal <sanj.aggarwal@gmail.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