Обсуждение: pg_dump not appending sequence to default values
I've come across an issue with pg_dump from 8.3.7 (running on Windows.) I'm using pg_dump to dump the schema only of the database for a system I'm currently displaying. The other day I had to re-create the database using the latest dump, and for a lot of the tables I now get the error "relation xxx does not exist" when adding a record, and I've found out it's because the nextval() default value isn't correctly re-created with the schema name. Here's an example - the table "tax" in the "product" schema has a default value for the primary key field of "nextval('product.tax_id'::regclass)" When I pg_dump the schema, the resulting SQL is: ... CREATE SCHEMA product; ... SET search_path = product, pg_catalog; ... CREATE SEQUENCE tax_id INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ... CREATE TABLE tax ( id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL ); Notice how "product.tax_id" has now become just "tax_id" so when I now insert a record into that table, it complains "relation 'tax_id' does not exist" and I have to manually edit it. Is this a known issue? I know a work-around is to include every schema name in the user's search path, but it's still annoying it's not re-created as it was originally. Thanks, Andy
Andy Shellam <andy-lists@networkmail.eu> writes: > When I pg_dump the schema, the resulting SQL is: > ... > CREATE SCHEMA product; > ... > SET search_path = product, pg_catalog; > ... > CREATE SEQUENCE tax_id > INCREMENT BY 1 > NO MAXVALUE > NO MINVALUE > CACHE 1; > ... > CREATE TABLE tax ( > id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL > ); > Notice how "product.tax_id" has now become just "tax_id" so when I now > insert a record into that table, it complains "relation 'tax_id' does > not exist" and I have to manually edit it. The reason it's printed as just 'tax_id' is that that relation should be first in the search_path at this point. Are you manually editing the dump in some way that screws that up? The underlying representation of regclass is an OID, not text, so once the default expression is created it's not subject to search path issues. It's not clear what you did to break it, but your description of the problem is based on faulty assumptions. regards, tom lane
Hi Tom > The reason it's printed as just 'tax_id' is that that relation should be > first in the search_path at this point. Yes, that's true - it's in the search path because (so I believe) pg_dump is adding a "SET search_path..." line before it carries out the commands in the schema, which works when the dump is restored, but when running as a normal user, the search path is the default ($user, public) and tax_id doesn't exist in the public schema (it exists as product.tax_id.) As I said a work-around is to set the user's search_path to include all schemas. > Are you manually editing the > dump in some way that screws that up? > Nope. I actually took the dump as I was writing the e-mail and verified that what I was saying was correct. The pg_dump command I used to create it was: pg_dump.exe --host=localhost --port=5432 --username=pgsql --file="C:\SVN\Aspire Platform\_developer\Platform Database.sql" --schema-only --format=p aspire_platform > The underlying representation of regclass is an OID, not text, so > once the default expression is created it's not subject to search path > issues. The default expression to begin with was "nextval('product.tax_id')" - either PostgreSQL or the GUI application converted it to "nextval('product.tax_id'::regclass)". When pg_dump dumps it out, it adds the "SET search_path = product, public" line and strips off the schema. > It's not clear what you did to break it, but your description > of the problem is based on faulty assumptions. > Forgive me if I have made any assumptions, but I cannot see where I can break it. The client application reports it as including the schema name in the nextval() clause, then after pg_dump has "dumped" it, within the SQL file it's gone and been replaced with a "SET search_path..." clause. When this SQL dump is restored, the schema is missing from the nextval() clause because of the "SET search_path" that pg_dump set. I don't know where else it can go wrong, unless there's some other switch I should be passing to pg_dump? Regards, Andy PS. Interestingly, pg_dump seems to be inconsistent in when it writes out schemas - this block of lines are right next to each other (and after the SET search_path line.) Notice how it's not qualified the first 3 lines, but the 4th it has? ALTER TABLE ONLY tax ALTER COLUMN id SET STATISTICS 0; ALTER TABLE ONLY tax ALTER COLUMN band_name SET STATISTICS 0; ALTER TABLE ONLY tax ALTER COLUMN tax_rate SET STATISTICS 0; ALTER TABLE product.tax OWNER TO my_user;
Andy Shellam <andy-lists@networkmail.eu> writes: > Yes, that's true - it's in the search path because (so I believe) > pg_dump is adding a "SET search_path..." line before it carries out the > commands in the schema, which works when the dump is restored, but when > running as a normal user, the search path is the default ($user, public) > and tax_id doesn't exist in the public schema (it exists as > product.tax_id.) As I said a work-around is to set the user's > search_path to include all schemas. No, it isn't. If the search_path was "product" when the table definition was loaded, then the regclass constant will remember that and the reference will be to product.tax_id. You are confusing what is displayed (which conditionally suppresses the schema name if it's not necessary based on your current search path) with what the reference actually is (which is always to a specific sequence regardless of name or schema). > The default expression to begin with was "nextval('product.tax_id')" - > either PostgreSQL or the GUI application converted it to > "nextval('product.tax_id'::regclass)". When pg_dump dumps it out, it > adds the "SET search_path = product, public" line and strips off the schema. And when you load it back in, it goes back to being what it was, ie an OID reference. You need to show us what you actually did, not an interpretation of what happened that is based on a faulty mental model. regards, tom lane
> No, it isn't. If the search_path was "product" when the table > definition was loaded, No it wasn't. When the table was initially created (from scratch not from the dump) the search path was the default of "$user", public. I've just re-created this using the following steps on a blank database: 1. Create a new database using a role with a default search path of "$user", public. 2. Create a schema in that database (myschema) 3. Create a sequence in the test schema (mysequence) 4. Create a table in the myschema schema (mytable) with an integer field that has a default value of nextval('myschema.mysequence'); - note this has to be qualified because the myschema schema is not in the search_path - confirmed with "nextval('mysequence')" and get the expected "relation mysequence does not exist" 5. Test adding a record to the table - OK 6. Dump the database using pg_dump (see my previous e-mail for the exact command) 7. Restore the database script against a clean database using the same user and search path of "$user", public - pg_dump has added the "SET search_path" at the appropriate points 8. Try and add a record to mytable - "ERROR: relation "mysequence" does not exist" > You are confusing what > is displayed (which conditionally suppresses the schema name if it's > not necessary based on your current search path) with what the reference > actually is (which is always to a specific sequence regardless of name > or schema). > I get what you mean now, Tom, that once the reference has been created it doesn't matter what's displayed because the OID reference has been saved, but from the test case above that doesn't appear to be the case. > You need to show us what you actually did, not an interpretation of > what happened that is based on a faulty mental model. > I've outlined the exact steps above using as minimal a test case as possible, and attached the associated SQL dump. Regards, Andy -- -- PostgreSQL database dump -- SET client_encoding = 'LATIN1'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -- -- Name: myschema; Type: SCHEMA; Schema: -; Owner: andyshel -- CREATE SCHEMA myschema; ALTER SCHEMA myschema OWNER TO andyshel; SET search_path = myschema, pg_catalog; -- -- Name: mysequence; Type: SEQUENCE; Schema: myschema; Owner: andyshel -- CREATE SEQUENCE mysequence INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE myschema.mysequence OWNER TO andyshel; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: mytable; Type: TABLE; Schema: myschema; Owner: andyshel; Tablespace: -- CREATE TABLE mytable ( id integer DEFAULT nextval('mysequence'::regclass), name character varying(30) ); ALTER TABLE myschema.mytable OWNER TO andyshel; -- -- Name: public; Type: ACL; Schema: -; Owner: pgsql -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM pgsql; GRANT ALL ON SCHEMA public TO pgsql; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete --
Andy Shellam <andy-lists@networkmail.eu> writes: > I've just re-created this using the following steps on a blank database: > 1. Create a new database using a role with a default search path of > "$user", public. > 2. Create a schema in that database (myschema) > 3. Create a sequence in the test schema (mysequence) > 4. Create a table in the myschema schema (mytable) with an integer field > that has a default value of nextval('myschema.mysequence'); - note this > has to be qualified because the myschema schema is not in the > search_path - confirmed with "nextval('mysequence')" and get the > expected "relation mysequence does not exist" > 5. Test adding a record to the table - OK > 6. Dump the database using pg_dump (see my previous e-mail for the exact > command) > 7. Restore the database script against a clean database using the same > user and search path of "$user", public - pg_dump has added the "SET > search_path" at the appropriate points > 8. Try and add a record to mytable - "ERROR: relation "mysequence" does > not exist" I did exactly the above, and it works as I expect. $ psql Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit regression=# create user myuser; CREATE ROLE regression=# create database mydb owner myuser; CREATE DATABASE regression=# \c mydb myuser You are now connected to database "mydb" as user "myuser". mydb=> create schema myschema; CREATE SCHEMA mydb=> create sequence myschema.mysequence; CREATE SEQUENCE mydb=> create table myschema.mytable (f1 int default nextval('myschema.mysequence')); CREATE TABLE mydb=> \d myschema.mytable Table "myschema.mytable" Column | Type | Modifiers --------+---------+-------------------------------------------------- f1 | integer | default nextval('myschema.mysequence'::regclass) mydb=> insert into myschema.mytable default values; INSERT 0 1 mydb=> \q $ pg_dump -U postgres -s mydb >mydb.dump $ cat mydb.dump -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; -- -- Name: myschema; Type: SCHEMA; Schema: -; Owner: myuser -- CREATE SCHEMA myschema; ALTER SCHEMA myschema OWNER TO myuser; SET search_path = myschema, pg_catalog; -- -- Name: mysequence; Type: SEQUENCE; Schema: myschema; Owner: myuser -- CREATE SEQUENCE mysequence INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE myschema.mysequence OWNER TO myuser; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: mytable; Type: TABLE; Schema: myschema; Owner: myuser; Tablespace: -- CREATE TABLE mytable ( f1 integer DEFAULT nextval('mysequence'::regclass) ); ALTER TABLE myschema.mytable OWNER TO myuser; -- -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- $ psql Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit regression=# create database mydb2 owner myuser; CREATE DATABASE regression=# \c mydb2 myuser You are now connected to database "mydb2" as user "myuser". mydb2=> \i mydb.dump SET SET SET SET SET CREATE SCHEMA ALTER SCHEMA SET CREATE SEQUENCE ALTER TABLE SET SET CREATE TABLE ALTER TABLE psql:mydb.dump:54: WARNING: no privileges could be revoked for "public" REVOKE psql:mydb.dump:55: WARNING: no privileges could be revoked for "public" REVOKE psql:mydb.dump:56: WARNING: no privileges were granted for "public" GRANT psql:mydb.dump:57: WARNING: no privileges were granted for "public" GRANT mydb2=> \c - You are now connected to database "mydb2". mydb2=> \d myschema.mytable Table "myschema.mytable" Column | Type | Modifiers --------+---------+-------------------------------------------------- f1 | integer | default nextval('myschema.mysequence'::regclass) mydb2=> insert into myschema.mytable default values; INSERT 0 1 mydb2=> select * from myschema.mytable; f1 ---- 1 (1 row) mydb2=> \q What are you doing differently? regards, tom lane