Thread: changing an update via rules - caught in recursion

--- don't think this made it through to the list ---
Hello,

I am having difficulty setting an on update rule which seems to be
caught in a recursive loop.  What is the best way to modify values sent
to an update query?

If it is helpful, here is the specific situation: The context is a table
with three columns assigning users to groups with the third column being
boolean to flag the primary group.  I would like to set an update rule
to enforce one primary group.  Any suggestions on how to do this, or
exactly how the code is getting trapped?  My code is below:

Thank you,
Lauren Matheson.

--change all others primarygroup to false when ours is true
CREATE RULE contactsecgrp_update_1pg_them
AS ON UPDATE TO contactsecuritygroup
WHERE NEW.primarygroup=true
DO
UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id =
NEW.contact_id and securitygroup_id != NEW.securitygroup_id;

--change our primarygroup to true when no true exists and we were false
CREATE RULE contactsecgrp_update_1pg_us
AS ON UPDATE TO contactsecuritygroup
WHERE NEW.primarygroup=false and (select count(contact_id) from
contactsecuritygroup where contact_id=NEW.contact_id AND
primarygroup=true)=0
DO
UPDATE contactsecuritygroup SET primarygroup=true WHERE contact_id =
NEW.contact_id and securitygroup_id = NEW.securitygroup_id;

Lauren Matheson <lmatheson@ivcf.ca> writes:
> CREATE RULE contactsecgrp_update_1pg_them
> AS ON UPDATE TO contactsecuritygroup
> WHERE NEW.primarygroup=true
> DO
> UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id =
> NEW.contact_id and securitygroup_id != NEW.securitygroup_id;

You can't do that, because a rule is a macro expanded during query
preparation, and the above is going to lead to infinite recursion
during macro expansion.  (No, the WHERE clause does not stop it,
because the actual value of WHERE is only checked at run time.)

You could make this sort of adjustment in a BEFORE UPDATE trigger,
though.

            regards, tom lane

Resolution for the archives:

I created a view essentially like:
CREATE VIEW editor_contact AS SELECT c.contact_id, c.password, (SELECT
csg.securitygroup_id FROM contactsecuritygroup csg WHERE
((csg.contact_id = c.contact_id) AND (csg.primarygroup = true)) ORDER BY
csg.securitygroup_id LIMIT 1) AS primarygroupname FROM contact c;

then a function to keep the unique primary group:
CREATE FUNCTION contactsecgrp_setprimarygrpname(varchar(255),varchar(255))
RETURNS bool
DECLARE
  new_contact_id ALIAS FOR $1;
  new_primarygroupname ALIAS FOR $2;
BEGIN
  DELETE from contactsecuritygroup WHERE contact_id=NEW_contact_id and securitygroup_id!=NEW_primarygroupname and
primarygroup=true;
  IF (SELECT count(contact_id) from contactsecuritygroup where securitygroup_id=NEW_primarygroupname and
contact_id=NEW_contact_id)=0
    THEN INSERT into contactsecuritygroup (contact_id, securitygroup_id, primarygroup) VALUES (NEW_contact_id,
NEW_primarygroupname,true); 
    ELSE UPDATE contactsecuritygroup set primarygroup=true WHERE contact_id=NEW_contact_id and
securitygroup_id=NEW_primarygroupname;
  END IF;
  RETURN true;
END;

then three rules to update the contact table and call the function to
update the grouping table:

CREATE RULE editor_contact_delete
AS ON DELETE TO editor_contact
DO INSTEAD
DELETE FROM contact WHERE (contact.contact_id = old.contact_id);

CREATE RULE editor_contact_insert
AS ON INSERT TO editor_contact
DO INSTEAD (
INSERT INTO contact (contact_id, password) VALUES (new.contact_id, new.password);
SELECT contactsecgrp_setprimarygrpname(new.contact_id, new.primarygroupname) AS contactsecgrp_setprimarygrpname;
);

CREATE RULE editor_contact_update
AS ON UPDATE TO editor_contact
DO INSTEAD (
UPDATE contact SET contact_id = new.contact_id, password = new.password WHERE (contact.contact_id = old.contact_id);
SELECT contactsecgrp_setprimarygrpname(new.contact_id, new.primarygroupname) AS contactsecgrp_setprimarygrpname;
);

A cascade on update/delete rule on the constraint between the contact
and group tables handles changes to contact_id, and removes group links
with their contact.

Peace,
Lauren.

On Wed, 2003-07-16 at 17:45, Tom Lane wrote:
> Lauren Matheson <lmatheson@ivcf.ca> writes:
> > CREATE RULE contactsecgrp_update_1pg_them
> > AS ON UPDATE TO contactsecuritygroup
> > WHERE NEW.primarygroup=true
> > DO
> > UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id =
> > NEW.contact_id and securitygroup_id != NEW.securitygroup_id;
>
> You can't do that, because a rule is a macro expanded during query
> preparation, and the above is going to lead to infinite recursion
> during macro expansion.  (No, the WHERE clause does not stop it,
> because the actual value of WHERE is only checked at run time.)
>
> You could make this sort of adjustment in a BEFORE UPDATE trigger,
> though.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match