[postgis-users] Creating a Flow Diagram

[Burgholzer,Robert]

This from a postgres post on a similar topic from Tom Lane:

> Rules are macros, which means that expansion has to terminate
> statically, not dynamically. For the particular purpose you seem to
> have here, it'd be a lot more manageable and a lot more efficient
> to use a BEFORE UPDATE trigger instead of a rule.

I will speculate on what this means, and let the more learned database gurus on the list correct me:

I think this means that a RULE must execute in such a way as to avoid calling itself again, hence the "static" termination.  The BEFORE UPDATE, or I think perhaps more advisable in this case, AFTER UPDATE trigger is allowed to pass itself on to subsequent procedures, whereas the RULE is not.

r.b.

-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Sufficool, Stanley
Sent:	Thu 6/5/2008 2:15 PM
To:	PostGIS Users Discussion
Cc:	
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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080605/c3384297/attachment.html>