functional index search path issue.

From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: functional index search path issue.
Date: 2025-07-05 11:45:43
Message-ID: CALdSSPjAdyv4Zh1m8Ss65T3hqCvjcOQm3sjkATP=71BacLK0+Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi.
I was inspecting the failure of pg_upgarde (15 to 16) on one of our clusters.
The issue was about pg_restore failing to create an index in new database

```

pg_restore: creating INDEX
"public.gar_addr_obj_plain_names_2_parents_idx" pg_restore: while
PROCESSING TOC: pg_restore: from TOC entry 4316; 1259 763099007 INDEX
gar_addr_obj_plain_names_2_parents_idx kyc_address_normalizer_owner
pg_restore: error: could not execute query:
ERROR: function immutable_array_to_string(text[]) does not exist
LINE 1: select to_tsvector('russian_stop_words_incl', immutable_arr... ^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: select to_tsvector('russian_stop_words_incl',
immutable_array_to_string($1 || $2))
CONTEXT: SQL function "address_names_to_tsvector" during inlining Command was:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('763099007'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('763099007'::pg_catalog.oid);
CREATE INDEX "gar_addr_obj_plain_names_2_parents_idx" ON
"public"."gar_address_objects_plain" USING "gin"
("public"."address_names_to_tsvector"("parent_names", "name")) WHERE
("cardinality"("parent_names") <= 2);

```

I don't know how the index was created, but I did my own reproduction
for postgresql 17->18 upgrade.

```
create extension pg_trgm;
create table t(i int);

create function f2 (i int) returns text language sql as $$ select '1221' $$;
create index on t using gin (f2(i) gin_trgm_ops);

create function f (i int) returns text language sql as $$ select '1' $$;
create or replace function f2 (i int) returns text language sql as $$
select f(i) $$;

```

now do upgrade:

/home/reshke/cpg/pgbin18/bin/pg_upgrade
--old-bindir=/home/reshke/cpg/pgbin17/bin/
--new-bindir=/home/reshke/cpg/pgbin18/bin/ --old-datadir=./db17
--new-datadir=./db18
....

....

```
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3245; 1259 16476 INDEX t_f2_idx reshke
pg_restore: error: could not execute query: ERROR: function
f(integer) does not exist
LINE 1: select f(i)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: select f(i)
CONTEXT: SQL function "f2" during inlining
Command was:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('16476'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('16476'::pg_catalog.oid);

```

Is this a postgres fault? One thing is with pg_upgrade failing for a
valid database, but maybe users should just re-create their indices
with fully-qualified names.

Second, `create or replace f2 command` does not fail, and it maybe
should, because If one tries to create an index staringfowrdly
(without function f2 replace), it will fail.

--
Best regards,
Kirill Reshke

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2025-07-05 14:38:47 Re: Unexpected behavior when setting "idle_replication_slot_timeout"
Previous Message Laurenz Albe 2025-07-05 08:47:56 Re: Unexpected behavior when setting "idle_replication_slot_timeout"