Finding recursive dependencies
| От | Joel Jacobson |
|---|---|
| Тема | Finding recursive dependencies |
| Дата | |
| Msg-id | AANLkTi=CaupB9X2hQUZ_XB=KWacrS6sDY5motDN2mQ6B@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Finding recursive dependencies
|
| Список | pgsql-general |
Hi,
--
Best regards,
Joel Jacobson
Glue Finance
I'm trying to find all recursive dependecies for an object, using the query in
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
В списке pgsql-general по дате отправления: