Обсуждение: 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

Joel Jacobson <joel@gluefinance.com> writes:
> 2011/1/2 Tom Lane <tgl@sss.pgh.pa.us>
>> The thing you're missing is that implicit dependencies are really
>> bidirectional:

> So, basically it's not possible to define a recursive query only making use
> of pg_depend to build an entire dependency tree of all oids?
> It appears to me it's necessary to join the object type specific tables,
> such as pg_rewrite, to build a complete tree?

No, that's nonsense.  The information is in pg_depend; it's just not
expressed in a way that makes it easy to scan it in a single recursive
query.  If you could do something like

    select base-case
    union all
    select objid ... where refobjid matches
    union all
    select refobjid ... where objid matches and deptype = 'i'

then it'd be easy, but you only get one UNION ALL per recursive query.

            regards, tom lane

2011/1/3 Tom Lane <tgl@sss.pgh.pa.us>:
>        select refobjid ... where objid matches and deptype = 'i'
> then it'd be easy, but you only get one UNION ALL per recursive query.

Ah, I see! Thanks for explaining. Now I get it.


--
Best regards,

Joel Jacobson
Glue Finance