Re: pg_depend explained

2011/1/12 Florian Pflug <fgp(at)phlo(dot)org>:
> I suggest you try to node-folding strategy and see how far it gets you.

Good suggestion! :-) That's exactly what I've been trying to do, but
failed miserably :-(

I have written a thorough description of my problem and put it on my github:

https://github.com/gluefinance/pov/tree/master/doc

In the example, pg_depend.sql, we want to reach the following
conclusions in an algorithmic way, only by using pg_depend as in-data:

table t1, function f1, sequence s1 have no dependencies, other than
the public schema
table t2 depends on t1 and table t3 depends on f1
view v1 depends on t1 and view v2 depends on t2
view v3 depends on both v1 and v2
view v4 depends on v3 and f1

The topological tree automatically generated from the data in
pg_depend is presented in pg_depend.dot, pg_depend.svg and
pg_depend.png.

The actual topological tree (possible order of creation) created
manually is presented in pg_depend_actual.dot, pg_depend_actual.svg
and pg_depend_actual.png.

The automatically created objects, such as primary key indexes,
constraints and triggers, have been ignored in this graph, as they are
implicitly created when creating the "base objects".

Objective:

Define a general algorithm taking ONLY the pg_depend data as input,
generating a valid topological directional graph, including at least
the nodes in pg_depend_actual.dot, but might as well include all
nodes, although it is not necessary, it's certainly won't hurt.

It will be necessary to look not only at the nodes (objects) and edges
(obj->refobj), but also the deptype for each edge.

List of files:

pg_depend.sql : A small but sufficient database model of tables,
sequences, functions and views.
pg_depend.dot : Digraph in DOT language (plaintext format), generated
automatically only by using data from pg_data.
pg_depend.png : PNG generated using GraphViz with pg_depend.dot as input
pg_depend.svg : SVG generated using GraphViz with pg_depend.dot as input

pg_depend_actual.dot : Digraph in DOT language (plaintext format),
generated manually, shows the actual possible order of creation, only
including the "base objects".
pg_depend_actual.png : PNG generated using GraphViz with
pg_depend_actual.dot as input
pg_depend_actual.svg : SVG generated using GraphViz with
pg_depend_actual.dot as input

--
Best regards,

Joel Jacobson
Glue Finance