Thread: pg_dump and search_path
<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>
On Mon, Aug 10, 2015 at 1:10 PM, Steve Thames <sthames42@gmail.com> wrote: > Please consider making the arbitrary determination of search_path by pg_dump > an optional behavior. Or better yet, just have it generate a backup that > accurately reflects the database it is backing up. Hmm, I don't think it's a question of making it optional. I think the current behavior is just a bug, and should be fixed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Aug 10, 2015 at 1:10 PM, Steve Thames <sthames42@gmail.com> wrote:
>> Please consider making the arbitrary determination of search_path by pg_dump
>> an optional behavior. Or better yet, just have it generate a backup that
>> accurately reflects the database it is backing up.
> Hmm, I don't think it's a question of making it optional. I think the
> current behavior is just a bug, and should be fixed.
It is not a bug, and as far as I can see what Steve is complaining about
isn't even pg_dump's behavior: it is just how regclass constants work.
regclass_out only qualifies the name if it wouldn't be found in the
current search path. This is a display behavior and has nothing to do
with what the actual value of the constant is:
regression=# create schema s1;
CREATE SCHEMA
regression=# create table s1.t1 (f1 serial);
CREATE TABLE
regression=# \d s1.t1 Table "s1.t1"Column | Type | Modifiers
--------+---------+----------------------------------------------------f1 | integer | not null default
nextval('s1.t1_f1_seq'::regclass)
regression=# set search_path = s1;
SET
regression=# \d s1.t1 Table "s1.t1"Column | Type | Modifiers
--------+---------+-------------------------------------------------f1 | integer | not null default
nextval('t1_f1_seq'::regclass)
Now, if pg_dump produced a file that failed to restore this state
of affairs correctly, that would be a bug. But I have seen no
evidence suggesting that it doesn't get it right. The way that the
commands are spelled in the dump file is an implementation detail.
regards, tom lane
Steve Thames wrote: > SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value > FROM pg_attribute AS a > JOIN pg_class AS c ON a.attrelid = c.oid > JOIN pg_namespace AS n ON c.relnamespace = n.oid > LEFT JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum > WHERE a.attnum > 0 > AND n.nspname = 'testschema' > AND c.relname = 'testtable'; Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin) instead. That's safe, for instance, if the sequence gets renamed. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin)
> instead. That's safe, for instance, if the sequence gets renamed.
It's probably past time we got rid of that column altogether. It just
wastes space and cycles. There was an argument for not being too quick
to get rid of it, but we deprecated it in 7.2 ... surely people have had
more than enough time to fix their applications.
regards, tom lane
Thank you gentlemen for clarifying this.
I found this problem when my database modeling tool saw a change in the
database (the nextval() parameters) after a database restore.
I guess the tool must be reading adsrc for this information.
Cheers,
Steve Thames
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 11, 2015 10:41 AM
To: Alvaro Herrera
Cc: Steve Thames; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] pg_dump and search_path
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin)
> instead. That's safe, for instance, if the sequence gets renamed.
It's probably past time we got rid of that column altogether. It just
wastes space and cycles. There was an argument for not being too quick to
get rid of it, but we deprecated it in 7.2 ... surely people have had more
than enough time to fix their applications.
regards, tom lane
Steve Thames wrote: > Thank you gentlemen for clarifying this. > > I found this problem when my database modeling tool saw a change in the > database (the nextval() parameters) after a database restore. > I guess the tool must be reading adsrc for this information. You can tell for sure by setting log_statement=all. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services