BUG #13179: pg_upgrade failure.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.paquier@gmail.com>
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);

-- ...

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 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. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs