How To Avoid Looping Trigger Calls In PostgreSQL 9.2.1

Question

I have a table:

CREATE TABLE field_data.soil_samples (
 pgid SERIAL NOT NULL,
 sample_id text,
 project_id text,
 utm_zone integer,
 utm_easting integer,
 utm_northing integer,
 wgs84_longitude double precision,
 wgs84_latitude double precision,
 yt_albers_geom geometry(Point,3578),
 CONSTRAINT soil_samples_pk PRIMARY KEY (pgid)
)

The PostGIS 2.0 geometry in yt_albers_geom is created using a trigger which fires on INSERTS against this table. If the record being inserted satisfies one of the following conditions, the geometry is generated:

Both wgs84_latitude and wgs84_longitude fields are not null
Each of utm_zone, utm_easting, and utm_northing are not null

Now, I am confused about how to do updates which achieve the following:

When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger
When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.
When an update is done to yt_albers_geom, all of the coordinate fields are updated.

It seems that any of these triggers would cause an infinite loop of trigger firing, correct?

Very nice, clear question! It's not that common that a question just has everything that's needed. Made me put in some effort. — Erwin Brandstetter, Dec 02 '12 at 03:44

Erwin Brandstetter · Accepted Answer · 2013-09-19T21:21:13.430

You can do this with standard triggers BEFORE UPDATE OF ... ON ....
The manual on CREATE TRIGGER informs:

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

And further down:

A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered.

Bold emphasis mine. So no infinite loops, because the the updates inside the trigger do not invoke another trigger.

Test case

Create test table (simplified, without irrelevant rows):

CREATE TABLE soil_samples (
  pgid SERIAL PRIMARY KEY

 ,utm_zone integer
 ,utm_easting integer
 ,utm_northing integer

 ,wgs84_longitude double precision
 ,wgs84_latitude double precision

 ,yt_albers_geom double precision
);

Dummy trigger for your first requirement:

When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger.

CREATE OR REPLACE FUNCTION trg_upbef_utm()  RETURNS trigger AS
$func$
BEGIN
   NEW.wgs84_latitude  := NEW.wgs84_latitude + 10;
   NEW.wgs84_longitude := NEW.wgs84_longitude + 10;
   NEW.yt_albers_geom  := NEW.yt_albers_geom + 10;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_utm
BEFORE UPDATE OF utm_zone, utm_easting, utm_northing ON soil_samples
FOR EACH ROW
WHEN (NEW.utm_zone     IS DISTINCT FROM OLD.utm_zone    OR
      NEW.utm_easting  IS DISTINCT FROM OLD.utm_easting OR
      NEW.utm_northing IS DISTINCT FROM OLD.utm_northing)  -- optional
EXECUTE PROCEDURE trg_upbef_utm();

The WHEN clause is optional. Prevents the trigger from firing when no value has actually changed.

Dummy trigger for your second requirement:

When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.

CREATE OR REPLACE FUNCTION trg_upbef_wgs84()  RETURNS trigger AS
$func$
BEGIN
   NEW.utm_zone       := NEW.utm_zone + 100;
   NEW.utm_easting    := NEW.utm_easting + 100;
   NEW.utm_northing   := NEW.utm_northing + 100;
   NEW.yt_albers_geom := NEW.yt_albers_geom + 100;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_wgs84
 BEFORE UPDATE OF wgs84_latitude, wgs84_longitude ON soil_samples
 FOR EACH ROW
 WHEN (NEW.wgs84_latitude  IS DISTINCT FROM OLD.wgs84_latitude OR
       NEW.wgs84_longitude IS DISTINCT FROM OLD.wgs84_longitude)  -- optional
 EXECUTE PROCEDURE trg_upbef_wgs84();

Trigger for third requirement along these lines ...

Test

INSERT INTO soil_samples VALUES (1, 1,1,1, 2,2, 3) RETURNING *;

Trigger upbef_utm: empty update, nothing happens:

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

Update with actual change: The second trigger upbef_wgs84 will not fire on UPDATE OF utm_zone!

UPDATE soil_samples SET utm_zone = 0 RETURNING *;

Trigger upbef_wgs84:

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle demo.

This answer is more than I could have hoped for! Testing on my machine works as you describe... modifications made in a BEFORE UPDATE do not themselves fire triggers on the affected columns. Thank you, thank you for the help. — Joebocop, Dec 02 '12 at 20:15
Also, can you tell me if it is quicker to be doing the IS DISTINCT FROM evaluation in the trigger rather than in the trigger function? IE: if I am testing a number of conditions, is it better to test each individually in their own trigger, and then to call a particular trigger function associated with the result of that evaluation, or should I write one trigger calling a single trigger function which contains a bunch of IF...ELSIF statements? Thanks again for the expertise! — Joebocop, Dec 11 '12 at 20:21
@Joebocop: That's the reason I added the `WHEN` condition to the trigger: it is generally faster. The reason is rather obvious: a simple expression is much cheaper to evaluate than calling a separate function for the job. You have to weigh higher maintenance cost against a small gain in performance before you start splitting things up. If you end up calling multiple triggers instead of one, then you are probably doing it wrong. — Erwin Brandstetter, Dec 11 '12 at 22:17
Further to your accepted solution, what is the expected outcome if an UPDATE statement is executed which updates wgs84_latitude, wgs84_longitude, and also yt_albers_geom? Do both triggers fire in alphabetical order? — Joebocop, Dec 12 '12 at 04:46
@Joebocop: Yes, triggers on the same event fire in alphabetical order. — Erwin Brandstetter, Dec 12 '12 at 13:40

How To Avoid Looping Trigger Calls In PostgreSQL 9.2.1

1 Answers1

Test case

Dummy trigger for your first requirement:

Dummy trigger for your second requirement:

Test

Linked