Re: set search_path in dump output considered harmful
От | Jim Nasby |
---|---|
Тема | Re: set search_path in dump output considered harmful |
Дата | |
Msg-id | 539A1598-1958-4B68-A63C-C2CF70669B51@pervasive.com обсуждение исходный текст |
Ответ на | set search_path in dump output considered harmful (Phil Frost <indigo@bitglue.com>) |
Список | 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 >
В списке pgsql-hackers по дате отправления: