Re: BUG #17456: pg_dump creates dump that does not fully respect operator schema location

On Tue, Apr 5, 2022 at 8:31 AM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17456
> Logged by: Andrew Grossman
> Email address: agrossman(at)gmail(dot)com
> PostgreSQL version: 14.2
> Operating system: MacOS 12.3
> Description:
>
> 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.

David J.