Re: BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to functioncall in index definition

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 +