BUG #17134: pg_restore ERROR: operator does not exist: util.ltree = util.ltree - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17134
Logged by:          Vinay Kumar
Email address:      vnykmr36@gmail.com
PostgreSQL version: 13.3
Operating system:   AWS AMI Amazon Linux 2018.03 & RHEL 8
Description:

Hi Team,

We are trying to upgrade one of our database with pg_upgrade --link option.
While upgrade happens we are facing an issue with trigger creation which
uses ltree.

The target version is 13.3 and also some other versions in 12 as well.

[ec2-user@ip-172-31-12-154 tmp]$ psql -V
psql (PostgreSQL) 13.3

postgres=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1
20170915 (Red Hat 7.2.1-2), 64-bit
(1 row)

Error observed during pg_upgrade:

pg_restore: error: could not execute query: ERROR:  operator does not exist:
util.ltree = util.ltree
LINE 1: ...ma"."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.
Command was: CREATE TRIGGER "path_aftr_trg" AFTER UPDATE ON "schema"."table"
FOR EACH ROW WHEN (("new"."path" IS DISTINCT FROM "old"."path")) EXECUTE
PROCEDURE "schema"."_update_path"();

Restoring the dump into another DB also results in similar error:

postgres=# CREATE TRIGGER "path_aftr_trg" AFTER UPDATE ON "schema"."table"
FOR EACH ROW WHEN (("new"."path" IS DISTINCT FROM "old"."path")) EXECUTE
PROCEDURE "schema"."_update_path"();
ERROR:  42883: operator does not exist: util.ltree = util.ltree
LINE 1: ...schema.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

Tested above trigger creation in version 11, 12 and 13 but all of them
resulted in failure until search path is set to schema which ends up
successful in creating the trigger.

postgres=# show search_path ;
   search_path
-----------------
 "$user", public
(1 row)

postgres=# set search_path = public, util;
SET

postgres=# show search_path ;
 search_path
--------------
 public, util
(1 row)

Successfully Created:
++++++++++++++++++

postgres=# CREATE TRIGGER "path_aftr_trg" AFTER UPDATE ON "schema"."table"
FOR EACH ROW WHEN (("new"."path" IS DISTINCT FROM "old"."path")) EXECUTE
PROCEDURE "schema"."_update_path"();
CREATE TRIGGER

postgres=# \dx ltree
                        List of installed extensions
 Name  | Version | Schema |                   Description
-------+---------+--------+-------------------------------------------------
 ltree | 1.2     | util   | data type for hierarchical tree-like
structures
(1 row)

A simple test fails as well:

postgres=# \d test
                 Table "public.test"
 Column |    Type    | Collation | Nullable | Default
--------+------------+-----------+----------+---------
 path   | util.ltree |           |          |
Indexes:
    "path_gist_idx" gist (path)
    "path_idx" btree (path)

postgres=# select * from public.test where path='Top';
ERROR:  42883: operator does not exist: util.ltree = unknown
LINE 1: select * from public.test where path='Top';
                                            ^
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

postgres=# set search_path = public, util;
SET
postgres=# select * from public.test where path='Top';
 path
------
 Top
(1 row)

Type casting works but we don't want this and this requires a lot of manual
labor:

postgres=# select * from public.test where path::text='Top';
 path
------
 Top
(1 row)

Any other easy method available to fix this issue with search_path?