ISTM that pg_dump needs to produce output that includes schema names,
though I'm not sure what side-effects that would have. I know one
issue is that it'd make it next to impossible to move things to a
different schema just be editing the dump.
On Jul 5, 2006, at 9:47 AM, Phil Frost wrote:
I've recently migrated one of my databases to using veil. This
creating a 'private' schema and moving all tables to it. Functions
remain in public, and secured views are created there which can be
accessed by normal users.
In doing so, I found to my extreme displeasure that although the
database continues to function flawlessly, I can no longer restore
produced by pg_dump even after hours of manual tweaking. In all cases,
this is due to search_path being frobbed during the restore.
CASE 1: serial column not in the same schema as associated table
create table a(i serial primary key);
create schema notpublic;
alter SEQUENCE a_i_seq set schema notpublic;
Attempting to restore the output of pg_dump on a database in which the
above has been executed will result in the error:
SET search_path = notpublic, pg_catalog;
-- Name: a_i_seq; Type: SEQUENCE SET; Schema: notpublic; Owner: pfrost
'i'), 1, false);
ERROR: relation "a" does not exist
CASE 2: default set to the serial sequence of another table
create schema private;
create table private.t(i serial primary key);
alter sequence private.t_i_seq set schema public;
create table public.t(i integer primary key default nextval
This is similar to case 1, and will encounter the same error first.
However, if that error is manually corrected, restoring the dump will
SET search_path = public, pg_catalog;
('private.t', 'i'), 1, false); -- manually fixed
CREATE TABLE t (
i integer DEFAULT nextval('t_i_seq'::regclass) NOT NULL
ERROR: relation "t_i_seq" does not exist
The problem here seems to be that although the sequence t_i_seq is in
schema public in the dumped database, restoring the dump places it in
CASE 3: functions containing unqualified function references
create schema private;
create function private.a(text) returns text language sql immutable
as $$ select $1 || 'a'; $$;
set search_path = public, private;
create function public.b(text) returns text language sql immutable
as $$ select a($1); $$;
create table foo(t text);
insert into foo values ('foo');
create index foo_idx on foo ((b(t)));
Restoring the dump of this database yields:
CREATE INDEX foo_idx ON foo USING btree (b(t));
ERROR: function a(text) does not exist
HINT: No function matches the given name and argument types. You
may need to add explicit type casts.
CONTEXT: SQL function "b" during startup
The way I encountered this actually has little to do with veil. The
function involved in my case takes as parameters some values from
columns of a table and returns a tsvector to be indexed by tsearch2. I
suspect this would be common practice if the tsearch2 documentation
not store the vector in an additional column.
CASE 4: functions using extension operators
Essentially the same as above, but the body of a function contains a
reference to an operator without specifying the schema with the
operator(schema.name) syntax. Again, contrib modules like tsearch2
great way to encounter this problem.
TIP 4: Have you searched our list archives?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend