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

On Thu, Aug 5, 2021 at 9:02 AM vinay kumar <vnykmr36(at)gmail(dot)com> wrote:

> 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.
>

Right...which is why you cannot rely on setting a search_path but must
instead arrange for everything, including the "=" operator, to be schema
qualified.

> But as per error message, it could be seen that the ltree type is found in
> util schema and the operator doesn't exist:
>

The ltree type wasn't "found" - its location is explicitly known to the
system because the recorded data type for that column (in
pg_attribute.atttype) includes an explicit schema location (in the related
pg_type table). So the system sees "util.ltree" as opposed to
"search_path=util; ltree".

The problem here is that "IS DISTINCT FROM" doesn't have any syntax for
schema qualification and so the stored representation of your expression is
impossible to record in a search_path agnostic way, unlike the create table
example above. "NEW" is just a composite type of that table and thus has
the same absolute references for the data types that comprise it. Hence my
ewrite suggestion that allows you to write "operator(util.=)" in the stored
expression thus providing a place for the schema qualification to exist (if
you just write = there the system might do the operator rewrite for you in
order to account for this dynamic.

> 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;
>

The cast to ltree there is unsafe, it needs to be "::util.ltree" (unless
the function itself is defined with util in the search_path).

> This seems to be a bug with the full path not being discovered or emitting
> the error even after it's discovered.
>

This is not a bug - it is a side-effect of a security related
implementation choice.

https://www.postgresql.org/support/security/CVE-2018-1058/

> 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:
>>
>
The convention on these lists is to inline reply (or bottom at least),
while trimming liberally.

David J.