Обсуждение: Sometimes pg_dump generates dump which is not restorable

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 /> 

"Dmitry Koterov" <dmitry@koterov.ru> writes:
> 3. The function a() calls any OTHER function b() from OTHER namespace (or
> uses operators from other namespaces), but does not specify the schema name,
> because it is in database search_path:

> CREATE FUNCTION a(i integer) RETURNS boolean  AS $$
> BEGIN
>     PERFORM b(); -- b() is is from "nsp" schema
>     RETURN true;
> END;$$ LANGUAGE plpgsql IMMUTABLE;

I think your function is broken.  You might want to fix it by attaching
a local search_path setting to it.
        regards, tom lane

Thank you for a possible solution.<br /><br />But what about the database which exists and works correctly (and
conformsall the standards from the documentation), but dump+restore sequence is failed for it? Does it mean that
pg_dumpshould be improved to pass dump+restore sequence?<br /><br />Besides that, for pg_dump has corresponding
behaviourCONSTRAINT = FOREIGN KEY. <br />For CONSTRAINT = CHECK - it hasn't.<br /><br /><br /><div
class="gmail_quote">OnThu, Nov 13, 2008 at 9:07 PM, Tom Lane <span dir="ltr"><<a
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">"DmitryKoterov" <<a href="mailto:dmitry@koterov.ru">dmitry@koterov.ru</a>> writes:<br /> > 3.
Thefunction a() calls any OTHER function b() from OTHER namespace (or<br /> > uses operators from other namespaces),
butdoes not specify the schema name,<br /> > because it is in database search_path:<br /><br /> > CREATE FUNCTION
a(iinteger) RETURNS boolean  AS $$<br /> > BEGIN<br /> >     PERFORM b(); -- b() is is from "nsp" schema<br />
>    RETURN true;<br /> > END;$$ LANGUAGE plpgsql IMMUTABLE;<br /><br /></div>I think your function is broken.
 Youmight want to fix it by attaching<br /> a local search_path setting to it.<br /><br />                      
 regards,tom lane<br /><font color="#888888"><br /> --<br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br /> 

"Dmitry Koterov" <dmitry@koterov.ru> writes:
> Thank you for a possible solution.
> But what about the database which exists and works correctly (and conforms
> all the standards from the documentation), but dump+restore sequence is
> failed for it? Does it mean that pg_dump should be improved to pass
> dump+restore sequence?

No matter what pg_dump does, it can never guarantee that a non-immutable
check constraint will still pass at restore time ... and that's
basically what you've got, if the check function is
search-path-sensitive.
        regards, tom lane

Oh, I understood you. Clearly, to surely avoid any side-effect in pg_dump, all IMMUTABLE functions must implicitly
assignsearch_path in develop time. It's not obvious, so I propose to include this in CONSTRAINT ... CHECK and CREATE
INDEXdocumentation. :-) Or - raise NOTICE if an IMMUTABLE function is used in CHECK or INDEX, but does not define
search_pathints arguments.<br /><br />Thanks!<br /><br /><br /><div class="gmail_quote">On Fri, Nov 14, 2008 at 7:25
PM,Tom Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><div class="Ih2E3d">"Dmitry Koterov" <<a
href="mailto:dmitry@koterov.ru">dmitry@koterov.ru</a>>writes:<br /></div><div class="Ih2E3d">> Thank you for a
possiblesolution.<br /> > But what about the database which exists and works correctly (and conforms<br /> > all
thestandards from the documentation), but dump+restore sequence is<br /> > failed for it? Does it mean that pg_dump
shouldbe improved to pass<br /> > dump+restore sequence?<br /><br /></div>No matter what pg_dump does, it can never
guaranteethat a non-immutable<br /> check constraint will still pass at restore time ... and that's<br /> basically
whatyou've got, if the check function is<br /> search-path-sensitive.<br /><br />                        regards, tom
lane<br/></blockquote></div><br />