Re: PostgreSQL 9.1 pg_dump setval() sets wrong value

Greg Donald <gdonald(at)gmail(dot)com> writes:
>> Are you
>> sure that the sequence is being used to insert those values into the
>> table?

> When I insert a new row into either of the tables I previously
> described, the sequence 'Current value' increments by one.

According to what? If you look directly at a sequence (eg with
"select * from sequence_name"), you'll see a "last_value" column, not
a 'Current value'. I suspect you're using some tool that is misleading
you somehow. Maybe it's referencing tables in the wrong schema, or even
connecting to the wrong database entirely.

In any case, it is highly, highly unlikely that pg_dump is the cause of
the problem. It's just reporting what it finds in the database. I'm
wondering whether the sequences are connected to the tables at all.
When I do something like
create table foo (id serial, x text);
I get all of the following items in pg_dump:

--
-- Name: foo; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE foo (
id integer NOT NULL,
x text
);

ALTER TABLE public.foo OWNER TO postgres;

--
-- Name: foo_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE foo_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER TABLE public.foo_id_seq OWNER TO postgres;

--
-- Name: foo_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE foo_id_seq OWNED BY foo.id;

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);

in addition to the setval and COPY commands that carry the data values.
I'm wondering how much of that is showing up in your dumps. In
particular, is the ALTER ... SET DEFAULT command there, and exactly how
does it spell the nextval() call?

regards, tom lane