[postgis-users] Creating a Flow Diagram

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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.