Re: pg_dump not appending sequence to default values - Mailing list pgsql-admin

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