Sometimes pg_dump generates dump which is not restorable
От | Dmitry Koterov |
---|---|
Тема | Sometimes pg_dump generates dump which is not restorable |
Дата | |
Msg-id | d7df81620811130823o2d2160d0x7a912ad332487387@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Sometimes pg_dump generates dump which is not restorable
|
Список | pgsql-hackers |
Hello.<br /><br />Why pg_dump dumps CONSTRAINT ... CHECK together with CREATE TABLE queries, but NOT at the end of dump file(as FOREIGN KEY)?<br />Sometimes it causes the generation of invalid dumps which cannot be restored. Details follow.<br/><br /><br />1. I use database-dedicated search_path:<br /><br />ALTER DATABASE d SET search_path TO nsp, public,pg_catalog;<br /><br /><br />2. I have a CHECK on table1 which calls a stored function:<br /><br /> CREATE TABLE table1(<br /> i integer,<br /> CONSTRAINT table1_chk CHECK ((a(i) = true))<br /> );<br /><br /><br />3. The functiona() calls any OTHER function b() from OTHER namespace (or uses operators from other namespaces), but does not specifythe schema name, because it is in database search_path:<br /><br />CREATE FUNCTION a(i integer) RETURNS boolean AS$$<br /> BEGIN<br /> PERFORM b(); -- b() is is from "nsp" schema<br /> RETURN true; <br />END;$$ LANGUAGE plpgsqlIMMUTABLE;<br /><br /><br />4. If I dump such schema using pg_dump, later this dump cannot be restored. Look the followingpiece of generated dump:<br /><br />SET search_path = public, pg_catalog;<br /><br />COPY table1 (i) FROM stdin;<br/>1<br />\.<br /><br />You see, when COPY is executed, data is inserted, and CHECK is called. So, function a() iscalled with "public, pg_catalog" search_path. <br />It is errorous!<br /><br /><br />Possible solutions:<br /><br />1.When generating CREATE TABLE dump query, DO NOT include CONSTRAINT ... CHECK clauses in it. Instead, use ALTER TABLEto add all checks AT THE END of dump, the same as it is done for foreign keys. I have already offered this above. Additionally,seems to me it will speed up the dump restoration.<br /><br />2. Replace "SET search_path = public, pg_catalog"to "SET search_path = public, pg_catalog, <all other database-dedicated search_pathes>". It's a worse way,kind a hack.<br /><br />
В списке pgsql-hackers по дате отправления: