Обсуждение: pg_dump and search_path
I earliest reference I found to this issue is here 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.
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
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