Обсуждение: BUG #10500: Cannot restore from a dump when some function is used in public shcema

The following bug has been logged on the website:

Bug reference:      10500
Logged by:          Nicolas Ross
Email address:      nicolas@cybercat.ca
PostgreSQL version: 9.3.4
Operating system:   CentOS 6.5
Description:

Hi !

I have encountered a problem when restoring a database from a dump made with
pg_dump.

I narrow it down to this simple exemple. Here's a pg_dump in plain text of
my test case :

CREATE SCHEMA intranet;
CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
SET search_path = public, pg_catalog;
CREATE FUNCTION cyunaccent(character varying) RETURNS character varying
    LANGUAGE sql IMMUTABLE
    AS $_$ SELECT unaccent(lower($1)); $_$;
-- At this stage, the cyunaccent function is created in the public shcema to
later be used by tables in all schema.
SET search_path = intranet, pg_catalog;
CREATE TABLE intranet.client (
    codeclient character varying(10) NOT NULL,
    noclient character varying(7),
    nomclient character varying(200) COLLATE pg_catalog."fr_CA"
);
ALTER TABLE ONLY client
    ADD CONSTRAINT client_pkey PRIMARY KEY (codeclient);

CREATE INDEX idx_clientnomclient ON client USING btree
(public.cyunaccent((lower((nomclient)::text))::character varying));

The pg_restore or psql won't create the index in the last sql, despite the
fact that the public schema is specified to reference the function.

The problem lies in the

SET search_path = intranet, pg_catalog;

call.

Changing it to

SET search_path = intranet, public, pg_catalog;

solves the problem.

I don't know if this is a bug in the way that the dump is produced or in the
way that the create index is done, but I beleive there is a bug here.

Hi,

On 2014-06-02 16:14:27 +0000, nicolas@cybercat.ca wrote:
> The pg_restore or psql won't create the index in the last sql, despite the
> fact that the public schema is specified to reference the function.

What exactly do you mean with "won't create"? Does it generate an error?
If so, what's that exactly?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund a écrit :
> Hi,
>
> On 2014-06-02 16:14:27 +0000, nicolas@cybercat.ca wrote:
>> The pg_restore or psql won't create the index in the last sql, despite the
>> fact that the public schema is specified to reference the function.
> What exactly do you mean with "won't create"? Does it generate an error?
> If so, what's that exactly?

Yes, sorry, here it is :

ERROR:  function unaccent(text) does not exist
LIGNE 1 :  SELECT unaccent(lower($1));
                   ^
ASTUCE : No function matches the given name and argument types. You
might need to add explicit type casts.
REQUÊTE :  SELECT unaccent(lower($1));
CONTEXTE : SQL function "cyunaccent" during inlining

********** Error **********

ERROR: function unaccent(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might
need to add explicit type casts.
Context: SQL function "cyunaccent" during inlining

nicolas@cybercat.ca writes:
> I narrow it down to this simple exemple. Here's a pg_dump in plain text of
> my test case :

> CREATE SCHEMA intranet;
> CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
> SET search_path = public, pg_catalog;
> CREATE FUNCTION cyunaccent(character varying) RETURNS character varying
>     LANGUAGE sql IMMUTABLE
>     AS $_$ SELECT unaccent(lower($1)); $_$;

This function is unsafe on its face: it does not specify what schema to
find unaccent() in.  You need to either explicitly schema-qualify:

     SELECT public.unaccent(lower($1));

(for good measure it'd be wise to qualify lower() as well), or else attach
a "SET search_path" clause to the function definition.

It's arguable whether the search path sensitivity of such functions is
a feature or a bug.  But there are people depending on the fact that they
can change the search path and get different results, so it's unlikely
we'd change the definition now.

            regards, tom lane

Tom Lane a écrit :

>> I narrow it down to this simple exemple. Here's a pg_dump in plain text of
>> my test case :
>> CREATE SCHEMA intranet;
>> CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;
>> SET search_path = public, pg_catalog;
>> CREATE FUNCTION cyunaccent(character varying) RETURNS character varying
>>      LANGUAGE sql IMMUTABLE
>>      AS $_$ SELECT unaccent(lower($1)); $_$;
> This function is unsafe on its face: it does not specify what schema to
> find unaccent() in.  You need to either explicitly schema-qualify:
>
>       SELECT public.unaccent(lower($1));
>
> (for good measure it'd be wise to qualify lower() as well), or else attach
> a "SET search_path" clause to the function definition.
>
> It's arguable whether the search path sensitivity of such functions is
> a feature or a bug.  But there are people depending on the fact that they
> can change the search path and get different results, so it's unlikely
> we'd change the definition now.
>
>             regards, tom lane
Oh !

Thanks for pointing that out. I modified my function definition and now
my restore can complete without an error.

Regards,