pg_dump and search_path - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal">I earliest reference I found to this issue is <a
href="http://postgresql.nabble.com/set-search-path-in-dump-output-considered-harmful-td1947594.html">here</a>and refers
tothe search_path being arbitrarily set in the file created by pg_dump. This is apparently still the case in 9.4.<p
class="MsoNormal"> <pclass="MsoNormal">I found this issue because I use SERIAL/BIGSERIAL columns and when I created
schema-specifictables in a schema other than the first listed in search_path the nextval() sequence references were
schema-qualified.<pclass="MsoNormal"> <p class="MsoNormal">When I created a backup file with pg_dump and then restored
usingpsql, the nextval() sequence references were no longer schema-qualified because the backup file set my table
schemaas the first schema in search_path. I saw the same result with pg_restore.<p class="MsoNormal"> <p
class="MsoNormal">Whilethe results of \d testschema.testtable shows the schema-qualified sequence name in nextval():<p
class="MsoNormal"> <prestyle="background:#EEEEEE"><span class="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">\</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">d
testschema</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">.</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">testtable</span></span><spanclass="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">;</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE"></span></span></pre><prestyle="background:#EEEEEE"><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">                                   </span></span><span class="kwd"><span
style="font-family:Consolas;color:darkblue;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">Table</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="str"><span style="font-family:Consolas;color:maroon;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">"testschema.testtable"</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE"></span></span></pre><prestyle="background:#EEEEEE"><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="kwd"><span style="font-family:Consolas;color:darkblue;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">Column</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">         
Type         </span></span><span class="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">                            Modifiers                            
</span></span></pre><prestyle="background:#EEEEEE"><span class="com"><span
style="font-family:Consolas;color:gray;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">--------+------------------------+-------------------------------------------------------------------</span></span><span
class="pln"><spanstyle="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE"></span></span></pre><prestyle="background:#EEEEEE"><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE"> id    
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
integer               </span></span><span class="pun"><span style="font-family:Consolas;color:black;border:none
windowtext1.0pt;padding:0in;background:#EEEEEE">|</span></span><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="kwd"><span style="font-family:Consolas;color:darkblue;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">not</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="kwd"><span style="font-family:Consolas;color:darkblue;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">null</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="kwd"><span style="font-family:Consolas;color:darkblue;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">default</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
nextval</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">(</span></span><spanclass="str"><span
style="font-family:Consolas;color:maroon;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">'testschema.testtable_id_seq'</span></span><spanclass="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">::</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">regclass</span></span><spanclass="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">)</span></span><spanstyle="font-family:Consolas;color:#393318"></span></pre><p
class="MsoNormal"> <pclass="MsoNormal"><p class="MsoNormal">The actual default read from pg_attrdef does not:<p
class="MsoNormal"> <pclass="MsoNormal" style="background:#EEEEEE"><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">  SELECT</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">a.attnum, n.nspname, c.relname, d.adsrc </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AS</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">default_value </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:black">     </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">FROM</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">pg_attribute </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AS</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">a </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:black">     </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">JOIN</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">pg_class     </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AS</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">c </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">ON</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">a.attrelid     = c.oid </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:black">     </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">JOIN</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">pg_namespace </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AS</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">n </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">ON</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">c.relnamespace = n.oid </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:darkblue">LEFT</span><span
style="font-size:10.0pt;font-family:Consolas;color:black"></span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">JOIN</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">pg_attrdef   </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AS</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">d </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">ON</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">d.adrelid      = c.oid </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AND</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">d.adnum = a.attnum </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:black">    </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">WHERE</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">a.attnum > </span><span
style="font-size:10.0pt;font-family:Consolas;color:maroon">0</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">  </span><p class="MsoNormal"
style="background:#EEEEEE"><spanstyle="font-size:10.0pt;font-family:Consolas;color:black">      </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AND</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">n.nspname = </span><span
style="font-size:10.0pt;font-family:Consolas;color:maroon">'testschema'</span><span
style="font-size:10.0pt;font-family:Consolas;color:black"> </span><p class="MsoNormal" style="background:#EEEEEE"><span
style="font-size:10.0pt;font-family:Consolas;color:black">      </span><span
style="font-size:10.0pt;font-family:Consolas;color:darkblue">AND</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">c.relname = </span><span
style="font-size:10.0pt;font-family:Consolas;color:maroon">‘testtable’</span><span
style="font-size:10.0pt;font-family:Consolas;color:black">;</span><pclass="MsoNormal" style="background:#EEEEEE"><span
style="font-size:10.0pt;font-family:Consolas;color:black"> </span><prestyle="background:#EEEEEE"><span
class="pln"><spanstyle="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">attnum</span></span><span class="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">  nspname  
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">  relname 
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">            
default_value            </span></span></pre><pre style="background:#EEEEEE"><span class="com"><span
style="font-family:Consolas;color:gray;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">--------+------------+-----------+---------------------------------------</span></span><span
class="pln"><spanstyle="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE"></span></span></pre><prestyle="background:#EEEEEE"><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">     
</span></span><spanclass="lit"><span style="font-family:Consolas;color:maroon;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">1</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE"> testschema
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE"> testtable
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
nextval</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">(</span></span><spanclass="str"><span
style="font-family:Consolas;color:maroon;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">'testtable_id_seq'</span></span><spanclass="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">::</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">regclass</span></span><spanclass="pun"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE">)</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext
1.0pt;padding:0in;background:#EEEEEE"></span></span></pre><prestyle="background:#EEEEEE"><span class="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">     
</span></span><spanclass="lit"><span style="font-family:Consolas;color:maroon;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">2</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE"> testschema
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE"> testtable
</span></span><spanclass="pun"><span style="font-family:Consolas;color:black;border:none windowtext
1.0pt;padding:0in;background:#EEEEEE">|</span></span><spanclass="pln"><span
style="font-family:Consolas;color:black;border:nonewindowtext 1.0pt;padding:0in;background:#EEEEEE">
</span></span><spanstyle="font-family:Consolas;color:#393318"></span></pre><p class="MsoNormal"> <p
class="MsoNormal">Thisinsistency is described <a
href="http://dba.stackexchange.com/questions/21150/default-value-of-serial-fields-changes-after-restore">here</a>.<p
class="MsoNormal"> <pclass="MsoNormal">This is not a documented behavior—at least I couldn’t find it and I searched
quitea bit. There was no indication to me that when I run pg_dump it will do something more than I asked it to do and
ittook me a while to figure out why. I solved the problem by setting the search_path as pg_dump does when creating the
databaseso now the restore does not create a different database than I did.<p class="MsoNormal"> <p
class="MsoNormal">Certainlyit would seem a bug that \d and a direct read from pg_attrdef give different results even
thoughpg_dump determining on its own what the search_path should be is no doubt an intended behavior. But it seems to
methis should be an option. I expected pg_dump to do what I asked it to do and when it did something other than that it
wasquite a headache.<p class="MsoNormal"> <p class="MsoNormal">What’s more, I like schema-qualified references. Schemas
arean effective database organization tool and I teach my people to use them and not depend on the search path as doing
soleads to sloppy and inconsistent thinking as well as coding.<p class="MsoNormal"> <p class="MsoNormal">Please
considermaking the arbitrary determination of search_path by pg_dump an optional behavior. Or better yet, just have it
generatea backup that accurately reflects the database it is backing up.<p class="MsoNormal"> <p class="MsoNormal">BTW,
Iam a huge fan of PostgreSQL.<p class="MsoNormal">Cheers!</div>