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 +
