From: Jonathan Knopp <pgsql@delegated.net>
To: pgsql-sql@postgresql.org
Subject: Way to stop recursion?
Date: Fri, 26 Nov 2004 13:03:38 -0800
Message-ID: <41A79A2A.6070606@delegated.net> (raw)
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
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: pgsql@delegated.net
Subject: Re: Way to stop recursion?
In-Reply-To: <41A79A2A.6070606@delegated.net>
* 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