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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: zgrannan(at)gmail(dot)com
Subject: BUG #15345: pg_upgrade from 9.6.10 to 10.5 fails due to function call in index definition
Date: 2018-08-22 05:08:36
Message-ID: 153491451667.1375.476837125433068071@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

---

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2018-08-22 05:24:31 Re: BUG #15343: Segmentation fault using pg_dump with --exclude-table if table contains identity column
Previous Message Thomas Munro 2018-08-22 04:47:30 Re: Re: Bug: ERROR: invalid cache ID: 42 CONTEXT: parallel worker