Date: Fri, 26 Nov 2004 23:21:51 +0100
To: pgsql-sql@postgresql.org
Subject: Re: Way to stop recursion?
References: <41A79A2A.6070606@delegated.net>
	<20041126213448.GB2312@phlogiston.dyndns.org>
From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
	<lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <opsh3e2ph5cq72hf@musicbox>
In-Reply-To: <20041126213448.GB2312@phlogiston.dyndns.org>
User-Agent: Opera M2/7.54 (Linux, build 751)


> You have to do this with a trigger.  The problem is that the rule is
> expanded inline like a macro, so you can't prevent the behaviour
> you're seeing.

	True, but you can get out of the hole in another way :

- Change the name of your table to "hidden_table"

- Create a view which is a duplicate of your table :
CREATE VIEW visible_table AS SELECT * FROM hidden_table;

-> Your application now accesses its data without realizing it goes  
through a view.

Now create a rule on this view, to make it update the real hidden_table.  
As the rule does not apply to hidden_table, it won't recurse.

Other solution (this similar to what Tom Lane proposed I think) :

Create a field common_id in your table, with
- an insert trigger which puts a SERIAL default value if there is no  
parent, or copies the parent's value if there is one
- an update trigger to copy the new parent's common_id whenever a child  
changes parent (if this ever occurs in your design)

Now create another table linking common_id to the 'common' value.

Create a view which joins the two, which emulates your current behaviour.
Create an ON UPDATE rule to the view which just changes one row in the  
link table.

If you do a lot of selects, solution #1 will be faster, if you do a lot of  
updates, #2 will win...

Just out of curiosity, what is this for ?


On Fri, 26 Nov 2004 16:34:48 -0500, Andrew Sullivan <ajs@crankycanuck.ca>  
wrote:

> On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote:
>> UPDATE rules work perfectly for what I need to do except I need them to
>> only run once, not try and recurse (which of course isn't allowedby
>> postgresql anyway). Triggers seem a less efficient way to do the same
>> thing, though I understand they would run recursively too. Here's the
>> table structure in question:
>
> You have to do this with a trigger.  The problem is that the rule is
> expanded inline like a macro, so you can't prevent the behaviour
> you're seeing.
>
> A
>