Postgresql ignoring 'when' condition on trigger

Question

A trigger seems to be ignoring the 'when condition' in my definition but I'm unsure why. I'm running the following:

create trigger trigger_update_candidate_location
after update on candidates
for each row
when (
  OLD.address1 is distinct from NEW.address1
  or
  OLD.address2 is distinct from NEW.address2
  or
  OLD.city is distinct from NEW.city
  or
  OLD.state is distinct from NEW.state
  or
  OLD.zip is distinct from NEW.zip
  or
  OLD.country is distinct from NEW.country

)
execute procedure entities.tf_update_candidate_location();

But when I check back in on it, I get the following:

-- auto-generated definition
create trigger trigger_update_candidate_location
  after update
  on candidates
  for each row
execute procedure tf_update_candidate_location();

This is problematic because the procedure I call ends up doing an update on the same table for different columns (lat/lng). Since the 'when' condition is ignored this crates an infinite loop.

My intention is to watch for address change, do a lookup on another table to get lat/lng values.

Postgresql version: 10.6 IDE: DataGrip 2018.1.3

Answer 1

How exactly do you create and "check back"? With datagrip?

The WHEN condition was added with Postgres 9.0. Some old (or poor) clients may be outdated. To be sure, check in pgsql with:

SELECT pg_get_triggerdef(oid, true)
FROM   pg_trigger
WHERE  tgrelid = 'candidates'::regclass  -- schema-qualify name to be sure
AND    NOT tgisinternal;

Any actual WHEN qualification is stored in internal format in pg_trigger.tgqual, btw. Details in the manual here.

Also what's your current search_path and what's the schema of table candidates?

It stands out that the table candidates is unqualified, while the trigger function entities.tf_update_candidate_location() has a schema-qualification ... You are not confusing tables of the same name in different DB schemas, are you?

Aside, you can simplify with this shorter, equivalent syntax:

create trigger trigger_update_candidate_location
after update on candidates   -- schema-qualify??
for each row
when (
   (OLD.address1, OLD.address2, OLD.city, OLD.state, OLD.zip, OLD.country)
   IS DISTINCT FROM
   (NEW.address1, NEW.address2, NEW.city, NEW.state, NEW.zip, NEW.country)
   )
execute procedure entities.tf_update_candidate_location();