From: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #13179: pg_upgrade failure. |
Date: | 2015-05-06 16:09:56 |
Message-ID: | CADkLM=cEhoQ+Z3AcDzA8b1+qSZvfJjnEZUoCa-RXZ1nN08Cyqg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(dot)paquier(at)gmail(dot)com>
wrote:
> On Tue, Apr 28, 2015 at 6:15 AM, <corey(dot)huinker(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2015-05-06 16:15:54 | Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated) |
Previous Message | Alvaro Herrera | 2015-05-06 14:34:18 | Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated) |