Re: Finding recursive dependencies

Joel Jacobson <joel@gluefinance.com> writes:
> I'm trying to find all recursive dependecies for an object, using the query
> in
> http://archives.postgresql.org/pgsql-general/2009-05/msg01192.php

Greg pointed out to start with that that query was unpolished (and,
in fact, basically untested ...)

I modified the query like this:

WITH RECURSIVE tree AS (
  SELECT 'a'::regclass::text AS tree,
  0 AS level,
  'pg_class'::regclass AS classid,
  'a'::regclass AS objid,
  0 as objsubid,
  ' '::"char" as deptype
 UNION ALL
SELECT tree || ' <-- ' || pg_describe_object(pg_depend.classid, pg_depend.objid, pg_depend.objsubid),
  level+1,
  pg_depend.classid,
  pg_depend.objid,
  pg_depend.objsubid,
  pg_depend.deptype
 FROM tree
 JOIN pg_depend ON ( tree.classid = pg_depend.refclassid
                     AND tree.objid = pg_depend.refobjid
                     AND (tree.objsubid = pg_depend.refobjsubid OR tree.objsubid = 0))
)
SELECT tree.tree, tree.deptype
FROM tree
WHERE level < 10
;

and got these results:

             tree             | deptype
------------------------------+---------
 a                            |
 a <-- rule _RETURN on view a | n
 a <-- rule _RETURN on view a | i
 a <-- type a                 | i
 a <-- rule _RETURN on view b | n
 a <-- type a <-- type a[]    | i
(6 rows)

or, starting from b,

             tree             | deptype
------------------------------+---------
 b                            |
 b <-- rule _RETURN on view b | n
 b <-- rule _RETURN on view b | i
 b <-- type b                 | i
 b <-- type b <-- type b[]    | i
(5 rows)

which is at least a little bit clearer to look at than what you had.

> I ran into problem with view dependencies.

The thing you're missing is that implicit dependencies are really
bidirectional: you can't delete either object without deleting the
other.  So you have to scan outwards across reverse implicit
dependencies, as well as forward dependencies of all types, if you
want to find everything that must be deleted when dropping a given
object.  I don't immediately see any way to do that with a single
recursive query :-(; you'd probably have to code up something in
plpgsql.

In the case at hand, b's view rule depends normally on a, and also
implicitly on b.

            regards, tom lane