Re: Dump/restore indexes and functions in public schema

Example:

in PG91:
CREATE FUNCTION function_y(x INT) RETURNS INT AS $$ SELECT $1*$1 $$
LANGUAGE SQL;
CREATE FUNCTION function_x(x INT) RETURNS INT AS $$ SELECT
function_y($1) $$ LANGUAGE SQL;
CREATE SCHEMA schema_a;
CREATE TABLE schema_a.table_a(i INT);
CREATE INDEX ON schema_a.table_a(function_x(i));
INSERT INTO schema_a.table_a VALUES(1),(9),(2);

Run pg_upgrade:
/usr/pgsql-9.2/bin/pg_upgrade --old-datadir
"/var/lib/pgsql/9.1/data" --new-datadir
"/var/lib/pgsql/9.2/data" --old-bindir "/usr/pgsql-9.1/bin"
--new-bindir "/usr/pgsql-9.2/bin"
...
Restoring database schema to new cluster *failure*

>From pg_upgrade_restore.log:
...
SET search_path = schema_a, pg_catalog;
...
CREATE INDEX table_a_function_x_idx ON table_a USING btree
(public.function_x(i));
psql:pg_upgrade_dump_db.sql:110: ERROR: function function_y(integer)
does not exist
LINE 1: SELECT function_y($1)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT function_y($1)
CONTEXT: SQL function "function_x" during inlining

(Same error is when only restore database.)

On Tue, Oct 9, 2012 at 4:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> marian krucina <marian(dot)krucina(at)gmail(dot)com> writes:
>> pg_upgrade failed on own server, because we used functions from public
>> schema in index. We install common functions (e.g. postgresql contrib)
>> to public schema. Tables and indexes are in another schema, and names
>> of functions without a schema name.
>
> Are you sure that has anything to do with schemas, or is it that you
> forgot to install the (updated versions of the) same contrib modules
> into the new installation?
>
> If not that, please provide a complete description of what you've got
> in your old database and the errors you got trying to upgrade. Also,
> exactly what old and new PG versions are you working with?
>
> regards, tom lane