I have a case where an AFTER ROW trigger has a condition comparing two ltree fields. The ltree extension is installed in a different schema than the triggered table is. Upon restoration, the following error is encountered:
psql:/tmp/bugreport.sql:93: ERROR: 42883: operator does not exist: util.ltree = util.ltree LINE 1: ...N my_schema.my_table FOR EACH ROW WHEN ((new.path IS DISTINC... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. LOCATION: op_error, parse_oper.c:731
The following sql will reproduce this case:
SET SEARCH_PATH=my_schema,util;
FOR EACH ROW WHEN ((new.path IS DISTINCT FROM old.path))
Yes, this is a known limitation extending from our securing the search_path in order to fix a CVE.
Casting to text works since it will use the system pg_catalog.=(text,text) operator.
The other option is to avoid the indirection caused by IS DISTINCT FROM and write out the equivalent expression verbosely:
not(new.path operator("util"."=") old.path) OR (new.path IS NULL AND old.path IS NULL)
Another option is to add a SET search_path clause on the CREATE FUNCTION and move the WHEN check inside the function. When the trigger invokes the function the attached search_path will then be put into force and the resolution of =(lpath,lpath) will find the one in the util schema. Unfortunately, the create trigger command doesn't have a similar capability to attach a local setting value to it.
