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
Re: BUG #10500: Cannot restore from a dump when some function is used in public shcema |
Список | 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 по дате отправления: