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&gt; 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

thanks, exactly what I needed

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-depend-OBJID-not-found-tp5787214p5787260.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general