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
involved
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
dumps
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;
SET
--
-- Name: a_i_seq; Type: SEQUENCE SET; Schema: notpublic; Owner: pfrost
--
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('a',
'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
('t_i_seq'));
This is similar to case 1, and will encounter the same error first.
However, if that error is manually corrected, restoring the dump will
yield:
SET search_path = public, pg_catalog;
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence
('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
schema private.
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
did
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
are a
great way to encounter this problem.
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
