Re: How to cascade information like the user roles ?

On Wed, Jan 20, 2010 at 10:13 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:

> Thanks a whole lot and some :)
>
> It's great that you actually did a working script.
> I find it tremendosly easier to learn with a working example than with some
> links to other documentation which makes or does not make sense.
>
> I've got a 8.4 server so both ways work nicely.
>
> Is there a way to prevent inheritance loops instead of denying parent_id >
> child_id ?
>

I'd suggest creating an ON INSERT+UPDATE trigger on app_role_inherits and
checking and disallowing loops there. If possible use a non-recursive method
to detect loop.

> Parallel inheritance has to work, though. 1 <-- 2 and 3 <-- 4
>

I didn't get this sentence!

>
> I found the function running into a stack overflow when I intetionally
> created a loop to check what'll happen. At least this is a predefined limit
> in max_stack_depth.
> The with recursive thingy just kept going which generally tends to be
> worse, I guess.
>

The WITH RECURSIVE is actually implemented non-recursively internally; see:
http://www.postgresql.org/docs/current/static/queries-with.html . So if you
have loops in there, there's nothing stopping the query from running
forever, except probably running out of disk for the intermediate table.

So either prevent loops in the data being queried, or put a WHERE clause
there that would prevent the query running forever.

Best regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device