Re: BUG #13179: pg_upgrade failure.

Good to know about set search_path from current!

However, this shows an issue: a database which appears functional to the
customer will fail to upgrade, and the only error message in the log file
complains of an object that does not exist, despite the same log file
showing that it does. Customers, already frustrated, might find that error
confusing, and might find the eventual explanation unsatisfying. After all,
the database worked for them, and they never do the thing that will make
their code break.

The desired behavior would be an upgraded database that continues to have
the problem of a function that will mal-function when a user changes their
search path.

Failing that, an improved error message would help.

Alternately, a warning message at function creation time when
ambiguously-pathed objects are referenced.

On Wed, May 6, 2015 at 2:19 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

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