Thread: BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to function callin index definition

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"));

On Wed, Aug 22, 2018 at 05:08:36AM +0000, PG Bug reporting form wrote:
> 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));

This is caused by security changes made in PG 10.3 and other minor
releases.  Please see this thread for an outline of the issue:

    https://www.postgresql.org/message-id/flat/152106914669.1223.5104148605998271987%40wrigleys.postgresql.org

The fix is to prefix the function call by 'public':

    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 public.subtract($1, $2)
    $_$;
    
    CREATE INDEX diff_things ON things(f(a,b));

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +