Thread: BUG #13179: pg_upgrade failure.
The following bug has been logged on the website: Bug reference: 13179 Logged by: Corey Huinker Email address: corey.huinker@gmail.com PostgreSQL version: 9.3.6 Operating system: Ubuntu 14.04 Description: 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. 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.
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
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
>
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);
>
>
> -- ...
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);
> >
> >
> > -- ...
>
>
On Wed, May 6, 2015 at 02:58:01PM -0400, Corey Huinker wrote: > 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. FYI, pg_upgrade is blindly calling pg_dump/pg_restore and erroring out if there is any failure, so any fix would have to be done at that level. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +