From: Gurjeet Singh <singh.gurjeet@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to cascade information like the user roles ?
Date: Fri, 22 Jan 2010 17:55:56 +0530
Message-ID: <65937bea1001220425r329d8f3v5dadbef26f97c5e9@mail.gmail.com> (raw)
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>
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.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: singh.gurjeet@gmail.com, maps.on@gmx.net
Subject: Re: How to cascade information like the user roles ?
In-Reply-To: <65937bea1001220425r329d8f3v5dadbef26f97c5e9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox