2

Trying to setup a trigger function to set geom on insert and update. The update part is working fine and response time is great (0.08sec)

However my insert triggers runs for 12-13 minutes for a single row. This is really unacceptable.

The function is to calculate the geom based on lat/lng for the new inserted rows.

What Am I doing wrong here...

  CREATE TRIGGER tr_insert_geom
      BEFORE INSERT
      ON schema.table1
      FOR EACH ROW
      EXECUTE PROCEDURE schema.fn_test_table_geo_update_event();
   -- ALTER TABLE schema.table1 DISABLE TRIGGER tr_insert_geom;


CREATE OR REPLACE FUNCTION schema.fn_test_table_geo_update_event()
  RETURNS trigger AS
$BODY$
  BEGIN  
    -- as this is an after trigger, NEW contains all the information we need even for INSERT
    UPDATE schame.table1 SET 
    geom = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 3857);
    RAISE NOTICE 'UPDATING geo data for %, [%,%]' , NEW.index_pk, NEW.latitude, NEW.longitude;  
    RETURN NEW; -- result is ignored since this is an AFTER trigger
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION locates_data.fn_test_table_geo_update_event()
  OWNER TO write_user;
  • Is your intention to update the entire table in that trigger? There's no predicate for the update statement. – Jay Cummins Feb 17 '17 at 17:31
  • yes, I want to update only the inserted row. How would I do that on an INSERT ? – Jonathan L Feb 20 '17 at 14:56
  • NEW.geom := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 3857); – Jay Cummins Feb 20 '17 at 17:03
  • NEW.geom = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 3857)ERROR: column "new" of relation "table1" does not exist SQL state: 42703 Context: PL/pgSQL function locates_data.fn_test_table_geo_update_event() line 4 at SQL statement – Jonathan L Feb 20 '17 at 18:20
  • it's a :=, not a =. – Jay Cummins Feb 21 '17 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.