Re: BUG #13179: pg_upgrade failure.

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