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.
