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

> 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
--