BUG #10500: Cannot restore from a dump when some function is used in public shcema

Поиск
Список
Период
Сортировка
От nicolas@cybercat.ca
Тема BUG #10500: Cannot restore from a dump when some function is used in public shcema
Дата
Msg-id 20140602161427.2797.72238@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema  (Andres Freund <andres@2ndquadrant.com>)
Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Serge Negodyuck
Дата:
Сообщение: Re: BUG #8673: Could not open file "pg_multixact/members/xxxx" on slave during hot_standby
Следующее
От: Andres Freund
Дата:
Сообщение: Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema