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
