On Wed, Jan 20, 2010 at 10:13 PM, Andreas <maps.on@gmx.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.