Skip site navigation (1) Skip section navigation (2)

Re: pg_depend explained

From: Joel Jacobson <joel(at)gluefinance(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Florian Pflug <fgp(at)phlo(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_depend explained
Date: 2011-01-12 19:06:24
Message-ID: AANLkTimFbgNB9wpbkULSxOdzPRAUgj8TjxYVfWmgfaaE@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Attachment: pg_depend_swapped.svg
Description: image/svg+xml (39.6 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-01-12 19:15:15
Subject: Re: pg_depend explained
Previous:From: Robert HaasDate: 2011-01-12 19:03:09
Subject: Re: Add support for logging the current role

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group