BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to function callin index definition - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15345
Logged by:          Zack Grannan
Email address:      zgrannan@gmail.com
PostgreSQL version: 10.5
Operating system:   NixOS
Description:

It appears that pg_upgrade fails when it encounters a table with an index
computed by a function that itself calls another function. The following is
a simple example:

CREATE TABLE things(a int, b int);

CREATE FUNCTION subtract(int, int) RETURNS int
LANGUAGE sql IMMUTABLE
AS $_$
SELECT $1 - $2
$_$;

CREATE FUNCTION f(int, int) RETURNS int
LANGUAGE sql IMMUTABLE
AS $_$
SELECT subtract($1, $2)
$_$;

CREATE INDEX diff_things ON things(f(a,b));

---

When attempting to run pg_upgrade, the following error is reported:

command:
"/nix/store/r6bwwif4877mbldmrbv3bsf8zw297jhv-postgresql-10.5/bin/pg_dump"
--host /home/zgrannan --port 50432 --username zgrannan --schema-only
--quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_13375.custom" 'dbname=postgres' >>
"pg_upgrade_dump_13375.log" 2>&1


command:
"/nix/store/r6bwwif4877mbldmrbv3bsf8zw297jhv-postgresql-10.5/bin/pg_restore"
--host /home/zgrannan --port 50432 --username zgrannan --exit-on-error
--verbose --dbname 'dbname=postgres' "pg_upgrade_dump_13375.custom" >>
"pg_upgrade_dump_13375.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating COMMENT "DATABASE "postgres""
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating FUNCTION "public.f(integer, integer)"
pg_restore: creating FUNCTION "public.subtract(integer, integer)"
pg_restore: creating TABLE "public.things"
pg_restore: creating INDEX "public.diff_things"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2976; 1259 16389 INDEX
diff_things zgrannan
pg_restore: [archiver (db)] could not execute query: ERROR:  function
subtract(integer, integer) does not exist
LINE 2: SELECT subtract($1, $2)
               ^
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY:  
SELECT subtract($1, $2)

CONTEXT:  SQL function "f" during inlining
    Command was: 
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('16389'::pg_catalog.oid);

CREATE INDEX "diff_things" ON "public"."things" USING "btree"
("public"."f"("a", "b"));