From: | Jim Nasby <jnasby(at)pervasive(dot)com> |
---|---|
To: | Phil Frost <indigo(at)bitglue(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: set search_path in dump output considered harmful |
Date: | 2006-07-07 21:09:56 |
Message-ID: | 539A1598-1958-4B68-A63C-C2CF70669B51@pervasive.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-07-07 21:18:45 | Re: DISTINCT/Optimizer question |
Previous Message | Jim Nasby | 2006-07-07 21:04:06 | Re: system info functions |