Stan, As a rule never put a rule on a table that involves updating/inserting into the table directly. It will always result in infinite recursion. Keep in mind rules do not do anything in an of themselves (where as triggers actually do do something directly), they merely rewrite queries so rewriting a query that involves rewriting a query when an update occurs requires rewriting itself.
In fact the only use case I can think of where you would ever put a rule on table is if you are using inherited tables and you put an instead of rule to redirect inserts to the child tables. Even in that case there are certain caveats with how it is used that you should be cautious of - that may still make a trigger more efficient. Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sufficool, Stanley Sent: Thursday, June 05, 2008 2:16 PM To: PostGIS Users Discussion Subject: RE: [postgis-users] Creating a Flow Diagram OK, I'm trying to come up with a solid solution to all this since it roughly applies to some things I'm doing with attached objects and location tracking. This rule is resulting in infinite recursion when there is no reason for the rule to even execute. Am I understanding the rules system wrong or should this be a trigger function? ------------------- create or replace rule entity_update AS ON update TO entity WHERE NOT (st_AsText(OLD.the_geom) ~= st_AsText(NEW.the_geom)) -- Only if geometry was modifed (I'm sure there is a more appropriate operator here) DO UPDATE entity SET the_geom = st_translate( -- Center the child on parent geometry st_translate( the_geom, st_x(st_centroid(NEW.the_geom)) - st_x(st_centroid(the_geom)), st_y(st_centroid(NEW.the_geom)) - st_y(st_centroid(the_geom)), st_z(st_centroid(NEW.the_geom)) - st_z(st_centroid(the_geom)) ), --Translate to relative position of parent ( st_xmax(NEW.the_geom) - st_xmin(NEW.the_geom) ) * ratio_x, ( st_ymax(NEW.the_geom) - st_ymin(NEW.the_geom) ) * ratio_y, ( st_zmax(NEW.the_geom) - st_zmin(NEW.the_geom) ) * ratio_z ) WHERE parent_entity_id = NEW.entity_id --Update all children of the modified parent record ; -- NOW LETS touch the parent update entity SET the_geom = st_translate(the_geom, 0, 0) where entity_id = (SELECT MIN(entity_id) FROM entity) ERROR: infinite recursion detected in rules for relation "entity" ********** Error ********** ERROR: infinite recursion detected in rules for relation "entity" SQL state: 42P17 ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
