pg_dump and search_path
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!
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers