From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: vnykmr36@gmail.com
Subject: BUG #17134: pg_restore ERROR: operator does not exist: util.ltree = util.ltree
Date: Thu, 05 Aug 2021 07:31:56 +0000
Message-ID: <17134-41b9adb547cb6e8e@postgresql.org> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, vnykmr36@gmail.com
Subject: Re: BUG #17134: pg_restore ERROR: operator does not exist: util.ltree = util.ltree
In-Reply-To: <17134-41b9adb547cb6e8e@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox