Re: BUG #13179: pg_upgrade failure.

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

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


-- ...







On Tue, Apr 28, 2015 at 1:41 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:

> On Tue, Apr 28, 2015 at 6:15 AM,  <corey.huinker@gmail.com> wrote:
> > I am experiencing a pg_upgrade failure 9.3->9.4, that seems to be the
> same
> > root cause as BUG #12465: Materialized view dump restoration issue.
>
> What is the error you are seeing?
>
> > I have a SQL immutable function created in the public schema, which
> returns
> > a custom enum type that also resides in the public schema. The type is
> > created correctly, and the function is created correctly.
> >
> > However, that function is called via a CROSS LATERAL JOIN inside a
> > materialized view which is in a different  schema. The restoration of
> that
> > materialized view fails.
>
> Do you have a self-contained test case that could be used to reproduce
> the failure?
> --
> Michael
>