PostGIS Trigger Function -- max stack depth reached

Question

Trying to create a dynamic trigger function in PostGIS but getting a "max_stack_depth" error message.

I have two tables in PostGRES/GIS that have geometry's testzone.carsurtest and political.localities and I'm trying to update the 'locality' field in testzone.carsurtest from the 'locality_name' field political.localities using ST_Intersects. I've successfully been able to create a trigger function specifically for testzone.carsurtest however I'd like to create a dynamic trigger function to use for multiple tables wanting to update from the locality table.

CREATE OR REPLACE FUNCTION tf_locality_test()
  RETURNS trigger AS
$BODY$

DECLARE
    tbl text := TG_TABLE_NAME;
    scm text := TG_TABLE_SCHEMA;

BEGIN

EXECUTE 'UPDATE '|| quote_ident(cast(scm as text)) ||'.'|| quote_ident(cast(tbl as text)) ||' AS dyntbl
        SET locality = political.locality.locality_name
        FROM  political.locality 
        WHERE ST_Intersects(dyntbl.wkb_geometry, political.locality.wkb_geometry)';

    RETURN NEW;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ALTER FUNCTION tf_locality_test() OWNER TO postgres;

When I create a new polygon record in QGIS i get the following error message:

>

 Could not commit changes to layer carsurtest
> 
> Errors: ERROR: 1 feature(s) not added.
>      Provider errors:
>       PostGIS error while adding features: ERROR:  stack depth limit exceeded
>     HINT:  Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate.
>     CONTEXT:  PL/pgSQL function "tf_locality_test" line 8 at EXECUTE statement
>     SQL statement "UPDATE test_zone.carsurtest AS dyntbl
>     SET locality = political.locality.locality_name
>     FROM  political.locality 
>     WHERE ST_Intersects(dyntbl.wkb_geometry, political.locality.wkb_geometry)"

Any suggestions would be greatly appreciated. Thanks, Chris

Answer 1

The problem here is that you have a BEFORE UPDATE trigger on a table that has a statement to update the same table: recursion. After a while there are so many triggers waiting for the EXECUTE statement to finish (each invoking the same trigger function) that the stack is filled up. Instead of executing a dynamic UPDATE in the trigger function, simply set the value of the field locality using the NEW implicit parameter:

CREATE OR REPLACE FUNCTION tf_locality_test() RETURNS trigger AS $BODY$
BEGIN
  SELECT loc.locality_name INTO NEW.locality
  FROM political.localities loc
  WHERE ST_Intersects(NEW.wkb_geometry, loc.wkb_geometry);
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Just so long as the other tables have the locality and wkb_geometry columns with adequate definitions then each can have a BEFORE UDPATE trigger that calls this same trigger function.