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

From: nicolas(at)cybercat(dot)ca
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #10500: Cannot restore from a dump when some function is used in public shcema
Date: 2014-06-02 16:14:27
Message-ID: 20140602161427.2797.72238@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 10500
Logged by: Nicolas Ross
Email address: nicolas(at)cybercat(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2014-06-02 16:48:01 Re: uninterruptable loop: concurrent delete in progress within table
Previous Message Serge Negodyuck 2014-06-02 14:56:42 Re: BUG #8673: Could not open file "pg_multixact/members/xxxx" on slave during hot_standby