Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 36BE1635617 for ; Fri, 22 Jan 2010 08:26:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59998-01-3 for ; Fri, 22 Jan 2010 12:26:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f190.google.com (mail-qy0-f190.google.com [209.85.221.190]) by mail.postgresql.org (Postfix) with ESMTP id 58E8163EEF1 for ; Fri, 22 Jan 2010 08:26:18 -0400 (AST) Received: by qyk28 with SMTP id 28so626184qyk.28 for ; Fri, 22 Jan 2010 04:26:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=2TxxfmbkSXSZBwTnqW0yE5BBBwOqGZLYysFvdGeSQeE=; b=B1vPxYe+DPhb82I7ARkfBoCsdLpPbIIBIBDLuJmG13lBWtkPAwIXNN55sx8cmhZUlW VE4wDdusqfs8xTSRgk+uqb/MsDazQiRZPbmVmE56o6j9XQbRQaZhwplm2XplH+X8PtIy BDS84tCVNDC3UfJigEkys0igaRZ8LEstvVcn0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=kqBPeEyBnySDX0nICv2REz9/6i8gOJI8jDXlfEOzY86MH++J6J63EK+XK/8ZoCi4Bj kgWv7llDQ03quvYEKqPd5rfm/vgSDzX7zBxcdnTIuHL05P9h45GNb8Dihju7/vgRWNjp q5UiZ8ZyfKCYDEr4zd5AdW9plQyPqmWzND+Zo= MIME-Version: 1.0 Received: by 10.220.124.106 with SMTP id t42mr228088vcr.92.1264163176665; Fri, 22 Jan 2010 04:26:16 -0800 (PST) In-Reply-To: <4B5732A0.1090006@gmx.net> References: <4B54FDF3.4070601@gmx.net> <92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com> <4B55E22F.80409@gmx.net> <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com> <65937bea1001192223o2a443c3dw9148352fc2bb76d4@mail.gmail.com> <4B5732A0.1090006@gmx.net> From: Gurjeet Singh Date: Fri, 22 Jan 2010 17:55:56 +0530 Message-ID: <65937bea1001220425r329d8f3v5dadbef26f97c5e9@mail.gmail.com> Subject: Re: How to cascade information like the user roles ? To: Andreas Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001636d34b3015526b047dbfed4f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/41 X-Sequence-Number: 33577 --001636d34b3015526b047dbfed4f Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jan 20, 2010 at 10:13 PM, Andreas 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.gurjeet@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device --001636d34b3015526b047dbfed4f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Wed, Jan 20, 2010 at 10:13 P= M, 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 =A0parent_id= > child_id ?

I'd suggest creating an ON IN= SERT+UPDATE trigger on app_role_inherits and checking and disallowing loops= there. If possible use a non-recursive method to detect loop.
=A0
Parallel inheritance has to work, though. =A0 1 =A0<-- 2 and 3 <-- 4<= br>

I didn't get this sentence!
=A0

I found the function running into a stack overflow when I intetionally crea= ted 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 implemen= ted non-recursively internally; see: http://www.postgresql.org/docs/curren= t/static/queries-with.html . So if you have loops in there, there's= nothing stopping the query from running forever, except probably running o= ut of disk for the intermediate table.

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

Best regards,<= br>
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Post= gres Company
http://www.enterprisedb.com
=
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
=
Mail sent from my BlackLaptop device
--001636d34b3015526b047dbfed4f--