BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to function call in index definition

Started by PG Bug reporting formover 7 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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

#2Bruce Momjian
bruce@momjian.us
In reply to: PG Bug reporting form (#1)
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@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:

/messages/by-id/152106914669.1223.5104148605998271987@wrigleys.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 +