Re: pg_depend explained

On Jan11, 2011, at 23:55 , Joel Jacobson wrote:
> 2011/1/11 Florian Pflug <fgp(at)phlo(dot)org>:
>> Could you give an example of the kind of trouble you're experiencing trying
>> to use a topological sort?
>
> Let's say you have a table t and a view v.
> The view v is defined as select * from t;
> If we put all objects in a tree, with the public schema as the root,
> both v and t will directly under the root, but in reality, v cannot be
> created before t.

AFAICS, you get the following dependencies (apart from the obvious
NORMAL dependencies from the pg_class entries of t and v on public)

t (pg_class) <--[INTERNAL]-- t (pg_type)
/°\
|
[NORMAL]
|
_RETURN (pg_rewrite)
| |
[NORMAL] [INTERNAL]
| |
\./ \./
v (pg_class) <--[INTERNAL]-- v (pg_type)

INTERNAL dependencies mark objects which spring into existence once the
referenced (target in my diagram) object is created. You can thus fold a
node I (the INTERNALly-depending object) into a node O (the object created
by the user) if there is an INTERNAL dependency from I to O.
The diagram then becomes

v (pg_class) --[NORMAL]--> t (pg_class)

Which correctly states that t must be created before v.

> This is the reason why a normal topological sort doesn't work.
> You have to look at the deptype and sort nodes having "internal" edges
> between them differently.

I suggest you try to node-folding strategy and see how far it gets you.

> I guess it's time for plan B, sorting based on oid, no biggie, it will
> work for my purpose, but it's damn ugly.

That will probably crash and burn once the OIDs have wrapped around once.

best regards,
Florian Pflug