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:
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