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