Postgresql: Trigger Function on After Insert/Updates: Stack Depth Limit Error

Question

I have a Postgresql database Trigger which is supposed to invoke a Function after either a new row is Insert or a row is Updated. Here is the Trigger:

CREATE TRIGGER firefly_new_geom
AFTER INSERT OR UPDATE 
ON public.entries FOR EACH STATEMENT 
EXECUTE PROCEDURE public.firefly_addupdate_creategeometry();

And here is the function which is being called:

   BEGIN
   UPDATE entries SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326) where geom is null;
   return new;
   END;

However, when I make any data change, directly using a client application, I get the message: Stack Depth Limit Exceeded. I thought, by having the 'where' clause I should not see this problem but still see this. Any idea? Thanks!

I think I got it! Will post my ANSWER soon. – IrfanClemson Mar 20 '14 at 18:55 — IrfanClemson, Mar 20 '14 at 18:55

score 7 · Accepted Answer · answered Mar 20 '14 at 19:02

I was in an infinite loop within the Trigger. Here is how I fixed the problem. Thanks to:

http://www.depesz.com/2012/02/01/waiting-for-9-2-trigger-depth/

The Trigger DDL:

CREATE TRIGGER firefly_new_geom
AFTER INSERT OR UPDATE 
ON public.entries FOR EACH STATEMENT 
EXECUTE PROCEDURE public.firefly_addupdate_creategeometry();

The Function:

 BEGIN
    IF pg_trigger_depth() <> 1 THEN
        RETURN NEW;
    END IF;
   UPDATE entries SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
   return new;
END;

score 2 · Answer 2 · answered Mar 20 '14 at 17:13

2

Looks like you created an infinite loop. Your trigger function calls firefly_addupdate_creategeometry() which does an update on the table which calls the trigger function which calls firefly_addupdate_creategeometry() which updates the table...

answered Mar 20 '14 at 17:13

Geoff Williams

1,320
10
15

Yes, looks like an infinite loop. But how to fix this? – IrfanClemson Mar 20 '14 at 17:27
what if you change your trigger to only fire for inserts? - assuming that the field doesn't get clobbered by external updates – Geoff Williams Mar 20 '14 at 17:28
It needs to work for both Updates and Insert; I can already make it work by just Inserts. There is this solution but not sure how it will fit in my code: http://stackoverflow.com/questions/14137932/update-a-table-with-a-trigger-after-update – IrfanClemson Mar 20 '14 at 17:30
and hang on this looks like postgis - are you by chance storing separate fields for latitude, longitude and geometry? If so this kinda defeats the purpose of using a spatial database and leads to situations where the data between these three fields is inconsistent as your trigger function highlights. I'd recommend you remove your longitude and latitude fields and just use the geom field if this is the case and have your insert sql code call st_geom(st_makepoint(...)) directly – Geoff Williams Mar 20 '14 at 17:34
Yes, it is Postgis. The data is already in the lat/long fields--hundreds of rows. There is a client application which needs to read 'geometry' data. So I created a new geometry type of field. Then I manually ran the Update command; that took care of the existing data. But, for future data changes, the geometry field needs to have values based on any new lat/long values. And hence the trigger. – IrfanClemson Mar 20 '14 at 17:38
Nightmare. Well the answer you linked to above seems to have a workaround using locking which could probably do what you want. Alternatively you could set a cron job or something to run periodically but then your going to have a window of time where the data is inconsistent. Perhaps a simpler way of solving this would be to move the geom column to a new table and do a join or view to get the full record. You could then update the geom column without ever having to worry about an update loop or working with record locking – Geoff Williams Mar 20 '14 at 17:43
1

I think I have found an answer: http://www.depesz.com/2012/02/01/waiting-for-9-2-trigger-depth/ – IrfanClemson Mar 20 '14 at 17:49
Geoff Williams: Thank you very much for your help. Some good ideas there from you! – IrfanClemson Mar 20 '14 at 17:54
No, I am not sure if the depth check is working when I do any updates using sql client. So the problem persists. – IrfanClemson Mar 20 '14 at 18:29
I think I got it finally! Had to remove the 'where' clause. Will post my Answer soon. – IrfanClemson Mar 20 '14 at 18:55

Postgresql: Trigger Function on After Insert/Updates: Stack Depth Limit Error

2 Answers2