Thread: pg_depend query
I am trying to write a query on the pg_depend table and it is confusing the hell out of me. Hopefully someone could help. I would like to know which views depend on another view. When I try to drop a view it gives me a list of all the views that need to be dropped in order for my view to be dropped/modified. I would like to write a query to get that list, store all the dependent views in an array, drop them all, change my view and then recreate all the other ones. I am spending way too much time going through and picking out the views based on that list, dropping them and recreating them. I will post my function to the list as soon as it is completed because I am sure there are plenty of other people who would benefit from it, but I'm having a very hard time getting started. Thank You Sim
"Sim Zacks" <sim@nospam.com> writes:
> I am trying to write a query on the pg_depend table and it is confusing the
> hell out of me. Hopefully someone could help.
> I would like to know which views depend on another view.
The dependencies are actually from the view's _RETURN rule to the
other view. So for example:
regression=# create view v1 as select * from int4_tbl;
CREATE VIEW
regression=# create view v2 as select * from v1;
CREATE VIEW
The only dependency v2 has explicitly is one on its namespace:
regression=# select classid::regclass,objid,objsubid,
regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend
regression-# where classid = 'pg_class'::regclass and objid = 'v2'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
----------+--------+----------+--------------+----------+-------------+---------
pg_class | 855926 | 0 | pg_namespace | 2200 | 0 | n
(1 row)
regression=# select nspname from pg_namespace where oid = 2200;
nspname
---------
public
(1 row)
But if we look for what *depends on* v2, we find:
egression=# select classid::regclass,objid,objsubid,
regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend
regression-# where refclassid = 'pg_class'::regclass and refobjid = 'v2'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
------------+--------+----------+------------+----------+-------------+---------
pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | n
pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | i
pg_type | 855927 | 0 | pg_class | 855926 | 0 | i
(3 rows)
regression=# select rulename from pg_rewrite where oid = 855928;
rulename
----------
_RETURN
(1 row)
The second of these is an implicit dependency from the view's _RETURN rule
to the view. If we now look for everything the _RETURN rule depends on:
regression=# select classid::regclass,objid,objsubid,
regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend
regression-# where classid = 'pg_rewrite'::regclass and objid = 855928;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
------------+--------+----------+------------+----------+-------------+---------
pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | n
pg_rewrite | 855928 | 0 | pg_class | 855923 | 1 | n
pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | i
(3 rows)
or more legibly
regression=# select classid::regclass,objid,objsubid,
regression-# refclassid::regclass,refobjid::regclass,refobjsubid,deptype from pg_depend
regression-# where classid = 'pg_rewrite'::regclass and objid = 855928;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
------------+--------+----------+------------+----------+-------------+---------
pg_rewrite | 855928 | 0 | pg_class | v2 | 0 | n
pg_rewrite | 855928 | 0 | pg_class | v1 | 1 | n
pg_rewrite | 855928 | 0 | pg_class | v2 | 0 | i
(3 rows)
The second of these rows shows that the _RETURN rule depends on v1's
first column, and it's that dependency that you are looking for.
You can probably collapse this into one query with some sub-selects,
but I'll leave that exercise to the reader.
The important point here is that an implicit dependency is sort of a
two-way street, because it indicates that the dependent object (here the
_RETURN rule) is an integral part of the referenced object (here the v2
view), and therefore a DELETE CASCADE will cascade in *both* directions.
Dropping v2 should obviously drop its _RETURN rule (that's the normal
forward direction of cascaded drops). But dropping something that the
_RETURN rule depends on forces dropping both the _RETURN rule and its
owning view (reverse direction). You can see this happening if you look
at DROP's notices:
regression=# drop view v1;
NOTICE: rule _RETURN on view v2 depends on view v1
NOTICE: view v2 depends on rule _RETURN on view v2
ERROR: cannot drop view v1 because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=#
The cascade went through the _RETURN rule, not directly to v2.
regards, tom lane
Thanks Tom, It took me the better part of the day but I banged out a fully working function. Sim "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:26493.1108581843@sss.pgh.pa.us... > "Sim Zacks" <sim@nospam.com> writes: > > I am trying to write a query on the pg_depend table and it is confusing the > > hell out of me. Hopefully someone could help. > > I would like to know which views depend on another view. > > The dependencies are actually from the view's _RETURN rule to the > other view. So for example: > > regression=# create view v1 as select * from int4_tbl; > CREATE VIEW > regression=# create view v2 as select * from v1; > CREATE VIEW > > The only dependency v2 has explicitly is one on its namespace: > > regression=# select classid::regclass,objid,objsubid, > regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend > regression-# where classid = 'pg_class'::regclass and objid = 'v2'::regclass; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > > ----------+--------+----------+--------------+----------+-------------+--- ------ > > pg_class | 855926 | 0 | pg_namespace | 2200 | 0 | n > (1 row) > > regression=# select nspname from pg_namespace where oid = 2200; > nspname > --------- > public > (1 row) > > But if we look for what *depends on* v2, we find: > > egression=# select classid::regclass,objid,objsubid, > regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend > regression-# where refclassid = 'pg_class'::regclass and refobjid = 'v2'::regclass; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > ------------+--------+----------+------------+----------+-------------+--- ------ > pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | n > pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | i > pg_type | 855927 | 0 | pg_class | 855926 | 0 | i > (3 rows) > > regression=# select rulename from pg_rewrite where oid = 855928; > rulename > ---------- > _RETURN > (1 row) > > The second of these is an implicit dependency from the view's _RETURN rule > to the view. If we now look for everything the _RETURN rule depends on: > > regression=# select classid::regclass,objid,objsubid, > regression-# refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend > regression-# where classid = 'pg_rewrite'::regclass and objid = 855928; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > ------------+--------+----------+------------+----------+-------------+--- ------ > pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | n > pg_rewrite | 855928 | 0 | pg_class | 855923 | 1 | n > pg_rewrite | 855928 | 0 | pg_class | 855926 | 0 | i > (3 rows) > > or more legibly > > regression=# select classid::regclass,objid,objsubid, > regression-# refclassid::regclass,refobjid::regclass,refobjsubid,deptype from pg_depend > regression-# where classid = 'pg_rewrite'::regclass and objid = 855928; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > ------------+--------+----------+------------+----------+-------------+--- ------ > pg_rewrite | 855928 | 0 | pg_class | v2 | 0 | n > pg_rewrite | 855928 | 0 | pg_class | v1 | 1 | n > pg_rewrite | 855928 | 0 | pg_class | v2 | 0 | i > (3 rows) > > The second of these rows shows that the _RETURN rule depends on v1's > first column, and it's that dependency that you are looking for. > > You can probably collapse this into one query with some sub-selects, > but I'll leave that exercise to the reader. > > The important point here is that an implicit dependency is sort of a > two-way street, because it indicates that the dependent object (here the > _RETURN rule) is an integral part of the referenced object (here the v2 > view), and therefore a DELETE CASCADE will cascade in *both* directions. > Dropping v2 should obviously drop its _RETURN rule (that's the normal > forward direction of cascaded drops). But dropping something that the > _RETURN rule depends on forces dropping both the _RETURN rule and its > owning view (reverse direction). You can see this happening if you look > at DROP's notices: > > regression=# drop view v1; > NOTICE: rule _RETURN on view v2 depends on view v1 > NOTICE: view v2 depends on rule _RETURN on view v2 > ERROR: cannot drop view v1 because other objects depend on it > HINT: Use DROP ... CASCADE to drop the dependent objects too. > regression=# > > The cascade went through the _RETURN rule, not directly to v2. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >