Re: Finding recursive dependencies

2011/1/2 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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