Re: pg_depend explained - Mailing list pgsql-hackers

2011/1/12 Tom Lane <tgl@sss.pgh.pa.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