Skip site navigation (1) Skip section navigation (2)

Way to stop recursion?

From: Jonathan Knopp <pgsql(at)delegated(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Way to stop recursion?
Date: 2004-11-26 21:03:38
Message-ID: 41A79A2A.6070606@delegated.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-11-26 21:31:11
Subject: Re: Way to stop recursion?
Previous:From: Tom LaneDate: 2004-11-26 18:14:07
Subject: Re: Type Inheritance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group