Re: BUG #17134: pg_restore ERROR: operator does not exist: util.ltree = util.ltree

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.

On Thu, Aug 5, 2021 at 9:04 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thu, Aug 5, 2021 at 1:49 AM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> 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.
>
>