Re: BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to function call 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(at)gmail(dot)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(at)momjian(dot)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 +