Re: BUG #13179: pg_upgrade failure. - Mailing list pgsql-bugs

On 5/6/15 12:09 PM, Corey Huinker wrote:
> Apologies for the delay, machine availability was an issue.
>
> Steps to reproduce.
>
> 1. Create a 9.3 instance.
> 2. Create a database in that instance, run this script in that instance:
>
>
>     begin;
>
>     create type custom_type_t as enum('one','two');
>
>     create function pointless_function() returns custom_type_t
>     language sql immutable as $$
>         select 'one'::custom_type_t;
>     $$;

This function definition does not record the current schema path, so the
function will fail to execute when run under a different schema setting.
 You should either explicitly qualify custom_type_t in the body or use
SET search_path FROM CURRENT in the definition.

>
>     create schema other_schema;
>
>     create materialized view other_schema.some_mview
>     as
>     select pointless_function() as pointless_value;
>
>     end;
>
>
> 3. Install 9.4 and attempt a pg_upgrade migration.
>
> Error log is as follows.
>
>
>     command: "/usr/lib/postgresql/9.4/bin/pg_dump" --host "/tmp" --port
>     50432 --username "postgres" --schema-only --quote-all-identifiers
>     --binary-upgrade --format=custom
>      --file="pg_upgrade_dump_16384.custom" "pg_upgrade_bug" >>
>     "pg_upgrade_dump_16384.log" 2>&1
>
>
>     command: "/usr/lib/postgresql/9.4/bin/pg_restore" --host "/tmp"
>     --port 50432 --username "postgres" --exit-on-error --verbose
>     --dbname "pg_upgrade_bug" "pg_upgrade_dump_16384.custom" >>
>     "pg_upgrade_dump_16384.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 SCHEMA other_schema
>     pg_restore: creating SCHEMA public
>     pg_restore: creating COMMENT SCHEMA "public"
>     pg_restore: creating TYPE custom_type_t
>     pg_restore: creating FUNCTION pointless_function()
>     pg_restore: creating MATERIALIZED VIEW some_mview
>     pg_restore: [archiver (db)] Error while PROCESSING TOC:
>     pg_restore: [archiver (db)] Error from TOC entry 171; 1259 16421
>     MATERIALIZED VIEW some_mview postgres
>     pg_restore: [archiver (db)] could not execute query: ERROR:  type
>     "custom_type_t" does not exist
>     LINE 2:     select 'one'::custom_type_t;
>                               ^
>     QUERY:
>         select 'one'::custom_type_t;
>
>     CONTEXT:  SQL function "pointless_function" during startup
>         Command was:
>     -- For binary upgrade, must preserve pg_type oid
>     SELECT binary_upgrade.set_next_pg_type_oid('16423'::pg_catalog.oid);
>
>
>     -- ...