Finding recursive dependencies
Hi,
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
I ran into problem with view dependencies.
In the example below, the view "b" depends on view "a".
How is it possible to interpret the result of the pg_depend query below,
to detect it's possible to drop "b", but dropping "a" is not possible, since
it depends on "b"?
Non of the objid or refobjid in pg_depend contain the oids of the views,
192092 nor 192096.
The view oids appears to be stored in pg_rewrite.ev_class though, but I find
it strange I cannot find them in pg_depend?
I'm sure there is an explanation to this and a simple way to solve my
problem.
How can a general query be constructed to find out all dependencies for any
given oid, regardless of its object class, listing all objects it depends
on recursively, or alternatively, listing all objects depending on the given
object recursively?
It would be best if such a query could be constructed only using pg_depend
and pg_class, without involving class specific tables such as pg_rewrite,
pg_constraint etc, as such a join would be quite expensive and
"non-general".
test=# CREATE VIEW a AS SELECT 1;
test=# CREATE VIEW b AS SELECT * FROM a;
test=# SELECT oid, relname FROM pg_class WHERE relname IN ('a','b');
oid | relname
--------+---------
192092 | a
192096 | b
(2 rows)
test=# WITH RECURSIVE tree AS (
test(# SELECT 'a'::regclass::text AS tree,
test(# 0 AS level,
test(# 'pg_class'::regclass AS classid,
test(# 'a'::regclass AS objid
test(# UNION ALL
test(# SELECT tree ||
test(# ' <-- ' ||
test(# pg_depend.classid::regclass || ' ' || pg_depend.objid ||
' ' || pg_depend.deptype,
test(# level+1,
test(# pg_depend.classid,
test(# pg_depend.objid
test(# FROM tree
test(# JOIN pg_depend ON ( tree.classid = pg_depend.refclassid
test(# AND tree.objid = pg_depend.refobjid)
test(# )
test-# SELECT tree.tree
test-# FROM tree
test-# WHERE level < 10
test-# ;
tree
---------------------------------------------
a
a <-- pg_rewrite 192095 n
a <-- pg_rewrite 192095 i
a <-- pg_type 192094 i
a <-- pg_rewrite 192099 n
a <-- pg_type 192094 i <-- pg_type 192093 i
(6 rows)
-- Same query for b:
tree
---------------------------------------------
b
b <-- pg_rewrite 192099 n
b <-- pg_rewrite 192099 i
b <-- pg_type 192098 i
b <-- pg_type 192098 i <-- pg_type 192097 i
(5 rows)
--
Best regards,
Joel Jacobson
Glue Finance
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
2011/1/2 Tom Lane <tgl@sss.pgh.pa.us>
Greg pointed out to start with that that query was unpolished (and,
in fact, basically untested ...)I modified the query like this:
which is at least a little bit clearer to look at than what you had.
Thanks a lot for the help!
I managed to partly solve the problem for views now anyway,
generated a topologically sorted create/drop sequence of views,
but it only joins the dependencies between views<->views and not all
objects.
I'll continue tomorrow including other dependencies as well, such as
functions.
Please have a look if you think I'm on the right track:
https://github.com/gluefinance/fsnapshot/blob/master/PLAYGROUND.sql
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.
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?
If so, that's fine, I just wondered if I had missed something making it
possible to avoid it.
--
Best regards,
Joel Jacobson
Glue Finance
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