Thread: Dump/restore indexes and functions in public schema

Hi,

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.
Can I do pg_upgrade (or pg_restore) with the indexes?
What is the best practice?

In documentation is: Users can then refer to these additional objects
by qualifying the names with a schema name, or they can put the
additional schemas into their search path, as they choose.

thank you
marian

marian krucina <marian.krucina@gmail.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

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@sss.pgh.pa.us> wrote:
> marian krucina <marian.krucina@gmail.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

marian krucina <marian.krucina@gmail.com> writes:
> 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);

Mph.  Well, actually what you've got there is a function that will break
anytime somebody looks at it sideways, anyhow.  You need to
schema-qualify the reference to function_y, or if you don't want to do
that for some reason, you could attach a "SET search_path" clause to the
definition of function_x.

I don't regard this as a Postgres bug, because index functions are
required to be immutable, and function_x fails that test because its
results vary depending on search_path.

            regards, tom lane