Thread: Way to stop recursion?
Been banging my head against the wall for days and starting to think there is no way to do what I need. Hoping someone on here can prove me wrong. 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: CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); INSERT INTO parent VALUES(1, 'adult', 0); INSERT INTO child VALUES(1, 1, 'kid 1', 0); INSERT INTO child VALUES(2, 1, 'kid 2', 0); What I need, is when "common" is changed for a parent, then that new value is reflected in "common" for all the children, ie: UPDATE parent SET cola='something', common=1 WHERE id=1; That in itself is no problem: CREATE RULE update_child_common AS ON UPDATE TO parent WHERE NEW.common!=OLD.common DO UPDATE child SET common=NEW.common WHERE parent_id=OLD.id; Problem is, when "common" is changed for a child, I need the parent and all siblings to reflect that value too, ie: UPDATE child SET cola='some value',common=2 WHERE id=2; If I could force recursion off, I could do that with: CREATE RULE update_common_from_child AS ON UPDATE TO child WHERE NEW.common!=OLD.common DO (UPDATE parent SET common=NEW.common WHERE id=NEW.parent_id;UPDATE child SET common=NEW.common WHERE parent_id=NEW.parent_id) As it stands, I can not find a way to do that. Any variation I try (using "flags", using INSTEAD, triggers) has led to recursion protection kicking in and postgresql refusing to run the query. I want to stay away from triggers if I can as I imagine they must be significantly less efficient when updating large numbers of parents and/or children at once (which happens frequently in the application), assuming a trigger could be made to do what I need at all. Hoping I'm missing something obvious... - Jonathan
Jonathan Knopp <pgsql@delegated.net> writes:
> CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1));
> CREATE TABLE child (id INT, parent_id INT, cola(1), common(1));
> What I need, is when "common" is changed for a parent, then that new
> value is reflected in "common" for all the children, ie:
> ...
> Problem is, when "common" is changed for a child, I need the parent and
> all siblings to reflect that value too, ie:
Seems to me that your real problem is a bogus database layout. If there
should only be one "common" value for a parent and children, then only
store one value ... that is, "common" should exist only in the parent.
You can if you like make a view that emulates the appearance of a child
table with a common column, viz
create view childview as select child.*, parent.common from child, parent where parent_id = parent.id;
and it would even be possible to make a rule that allows updating this
view.
regards, tom lane
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 -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun
On Fri, Nov 26, 2004 at 04:31:11PM -0500, Tom Lane wrote: > > Seems to me that your real problem is a bogus database layout. If there > should only be one "common" value for a parent and children, then only > store one value ... that is, "common" should exist only in the parent. Tom's answers always make me realise that I should think harder before I talk. He's right, of course: one common value means store it once. A -- Andrew Sullivan | ajs@crankycanuck.ca I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin
Tom Lane wrote: >>CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); >>CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); > >>What I need, is when "common" is changed for a parent, then that new >>value is reflected in "common" for all the children, ie: >>... >>Problem is, when "common" is changed for a child, I need the parent and >>all siblings to reflect that value too, ie: > > Seems to me that your real problem is a bogus database layout. If there > should only be one "common" value for a parent and children, then only > store one value ... that is, "common" should exist only in the parent. > ... Sorry, I should have mentioned that there is a lot more to the design that makes this replication necessary, including another two levels to the tree plus the ability to have orphaned children.
> 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 >
Pierre-Frédéric Caillaud wrote: > - 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... The "hidden table" method should work just fine. Ingenius idea, thank you! > Just out of curiosity, what is this for ? The actual application has companies instead of parents, employees instead of children, then emails as children of employees and/or companies, and folders as parents of companies and employees. The "common" field (in all 4 layers) are a pair of permissions flags. May I humbly suggest two possible todo's for postgreSQL: a simple flag to suppress recursion (easier/more powerful way of doing the above), and/or more direct access to query rewriting. Seems right now rules require you to rewrite queries while partially blind to them. Being able to rewrite queries in statement triggers similar to what can be done with row triggers would be very nice too.
Jonathan Knopp wrote: > > Sorry, I should have mentioned that there is a lot more to the design > that makes this replication necessary, including another two levels to > the tree plus the ability to have orphaned children. > My first thought was "Dude, use a VIEW...." In database design, the SPOT principle applies. *Always* enforce a Single Point Of Truth. If that doesn't seem to be possible, rethink how the data is used and look at how to ensure that there is only ONE authoritative storeage for each piece of transactional data. (Yes, sometimes we get away from this with OLAP installations but the data is not generally being updated there.) In this case, I would create a view (with appropriate rules) which would automatically populate the common fields from the parent if it exists. The issue should not be one of storage but of presentation. Best Wishes, Chris Travers Metatron Technology Consulting > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >