"Sim Zacks" <sim@xxxxxxxxxx> 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
