Re: pg_depend explained

2011/1/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> I've sometimes found it useful to think of internal dependencies as
> acting like normal dependencies pointing in the other direction.
> I'm not sure that would do much to solve your problem, but it might
> be worth trying.

Tom, you are a genious! No, seriously, I mean it, this is awesome, it
worked! YES! You totally saved my day! Thank you! Finally! I'm so
happy! :-) :-) :-)

This was the little piece of code:

CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN
--- Swap edges
ELSE
-- Do not swap edges
END

Look at the attached svg graph how beautiful the automatically
generated graph look like now! :-)

The tsort of the objects now sort all the normal objects in a creatable order!

Here is the result of the tsort (only including the normal objects
(the one I care about (I don't have to create the internal/auto
objects, nor drop them))):

The query below can both produce a DOT-format graph and a tsort of the
creatable order of objects:

WITH
NewObjectOids AS (
SELECT * FROM pg_depend WHERE deptype <> 'p'
EXCEPT
SELECT * FROM pg_depend_before
),
NewObjectOidsAggDepType AS (
SELECT classid,objid,objsubid,refclassid,refobjid,refobjsubid,array_to_string(array_agg(deptype),'')
AS deptype
FROM NewObjectOids GROUP BY
classid,objid,objsubid,refclassid,refobjid,refobjsubid
),
NewObjects AS (
SELECT
CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN
pg_describe_object(classid,objid,0) || ' ' || classid
|| '.' || objid
ELSE
pg_describe_object(refclassid,refobjid,0) || ' ' || refclassid
|| '.' || refobjid
END AS RefObj,
CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN
pg_describe_object(refclassid,refobjid,0) || ' ' || refclassid
|| '.' || refobjid
ELSE
pg_describe_object(classid,objid,0) || ' ' || classid
|| '.' || objid
END AS Obj,
DepType
FROM NewObjectOidsAggDepType
),
DepDigraph AS (
SELECT DISTINCT RefObj, Obj, DepType FROM NewObjects
WHERE RefObj <> Obj
),
DotFormat AS (
SELECT 'digraph pg_depend {' AS diagraph
UNION ALL
SELECT ' "'
|| RefObj
|| '" -> "'
|| Obj
|| '" [' || CASE
WHEN array_to_string(array_agg(DepType),'') = 'n'
THEN 'color=black'
WHEN array_to_string(array_agg(DepType),'') = 'i'
THEN 'color=red'
WHEN array_to_string(array_agg(DepType),'') = 'a'
THEN 'color=blue'
WHEN array_to_string(array_agg(DepType),'') ~
'^(ni|in)$' THEN 'color=green'
WHEN array_to_string(array_agg(DepType),'') ~
'^(na|an)$' THEN 'color=yellow'
ELSE 'style=dotted'
END
|| ' label=' || array_to_string(array_agg(DepType),'') || ']'
FROM DepDigraph GROUP BY RefObj, Obj
UNION ALL
SELECT '}'
),
TopoSort AS (SELECT unnest FROM unnest((SELECT
tsort(array_to_string(array_agg(RefObj || ';' || Obj),';'),';',2) FROM
DepDigraph)))
SELECT * FROM TopoSort;

sequence s1 1259.23359
function f1(integer) 1255.23358
table t3 1259.23371
table t1 1259.23353
view v1 1259.23378
table t2 1259.23361
view v2 1259.23382
view v3 1259.23386
view v4 1259.23390

--
Best regards,

Joel Jacobson
Glue Finance