I earliest reference I found to this issue is here
<http://postgresql.nabble.com/set-search-path-in-dump-output-considered-harm
ful-td1947594.html> and refers to the search_path being arbitrarily set in
the file created by pg_dump. This is apparently still the case in 9.4.
I found this issue because I use SERIAL/BIGSERIAL columns and when I created
schema-specific tables in a schema other than the first listed in
search_path the nextval() sequence references were schema-qualified.
When I created a backup file with pg_dump and then restored using psql, the
nextval() sequence references were no longer schema-qualified because the
backup file set my table schema as the first schema in search_path. I saw
the same result with pg_restore.
While the results of \d testschema.testtable shows the schema-qualified
sequence name in nextval():
\d testschema.testtable;
Table "testschema.testtable"
Column | Type | Modifiers
--------+------------------------+------------------------------------------
-------------------------
id | integer | not null default
nextval('testschema.testtable_id_seq'::regclass)
The actual default read from pg_attrdef does not:
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';
attnum | nspname | relname | default_value
--------+------------+-----------+---------------------------------------
1 | testschema | testtable | nextval('testtable_id_seq'::regclass)
2 | testschema | testtable |
This insistency is described here
<http://dba.stackexchange.com/questions/21150/default-value-of-serial-fields
-changes-after-restore> .
This is not a documented behavior-at least I couldn't find it and I searched
quite a 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 it took me a while to figure out
why. I solved the problem by setting the search_path as pg_dump does when
creating the database so now the restore does not create a different
database than I did.
Certainly it would seem a bug that \d and a direct read from pg_attrdef give
different results even though pg_dump determining on its own what the
search_path should be is no doubt an intended behavior. But it seems to me
this 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 was quite a headache.
What's more, I like schema-qualified references. Schemas are an effective
database organization tool and I teach my people to use them and not depend
on the search path as doing so leads to sloppy and inconsistent thinking as
well as coding.
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.
BTW, I am a huge fan of PostgreSQL.
Cheers!
