Thread

1. functional index search path issue.

   Kirill Reshke <reshkekirill@gmail.com> — 2025-07-05T11:45:43Z

   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
   
   
   
   

2. Re: functional index search path issue.

   David G. Johnston <david.g.johnston@gmail.com> — 2025-07-05T14:53:13Z

   On Saturday, July 5, 2025, Kirill Reshke <reshkekirill@gmail.com> wrote:
   
   >
   > 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.
   >
   
   Yes, functions you intend for the server to execute during dump/restore or
   other administrative routines where it sets up a safe searh_path need to be
   able to resolve all identifiers without the benefit of a session-defined
   search_path beyond pg_catalog.  Public is not part of the safe search_path.
   
   David J.