Thanks David for replying back!
Adding search_path has resolved this issue. But the problem is as follows where we won't be able to set search_path:
1> When trying to restore the data into a new DB server.
2> When we do an upgrade.
But as per error message, it could be seen that the ltree type is found in util schema and the operator doesn't exist:
ERROR: 42883: operator does not exist: util.ltree = util.ltree
With in the function, we have below entry for NEW."path" where we provide schema.type:
NEW."path" = util.ltree_pathify(NEW."id"::TEXT)::ltree;
This seems to be a bug with the full path not being discovered or emitting the error even after it's discovered.
Any other easy method available to fix this issue with search_path?
The direct solution here is to rewrite the expression, avoiding "IS DISTINCT FROM":
not("new"."path" operator("util".=) "old"."path") and (coalesce("new"."path", "old"."path") is not null)
This is needed because you need to schema-qualify the location of the = operator which requires using the "operator(...)" syntax.
That said, it might be easier, if less performant, to remove the WHEN condition and place an equivalent expression within the trigger function itself. If the function is defined with a "SET search_path" clause that should ensure that the function body is evaluated with the "util" schema in the search_path and thus the "util".= operator will be found.
David J.
