Re: PostgreSQL 9.1 pg_dump setval() sets wrong value

On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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?

PgAdmin3. I'm assuming 'Current value' means the sequence 'START'
value, they are just using a different label than the official
PostgreSQL terminology.. I guess.

> In any case, it is highly, highly unlikely that pg_dump is the cause of
> the problem.

Well ok.. all I know is it did not work this way a few days ago when I
was running 8.4. I have been using 8.4 for nearly a year on this one
project. I have been pulling copies of the production database all
that time using pg_dump and they all restored fine up until I upgraded
to 9.1.1. Now I get incorrect setval() values in my pg_dump backups.
It's the same data, same tables, same sequences, live in production,
working fine.

When I dumped a copy from 8.4 to go live in 9.1 it went in fine, no
setval() value issues at all. It is only now that I have the 9.1
version of pg_dump in place that restore problems are occurring.

> It's just reporting what it finds in the database.

Well, not in my case. In my database my sequences do not contains
these incorrect '1' values I see in some of the setval() calls.

> I'm
> wondering whether the sequences are connected to the tables at all.

They were made using 'id SERIAL NOT NULL'.

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

grep nextval backup_20111223013539.sql | wc -l
66

Exactly correct for how many tables I have that use sequences.

One of them looks like this for example:

ALTER TABLE state ALTER COLUMN id SET DEFAULT nextval('state_id_seq'::regclass);

And then for the setval() calls

grep setval backup_20111223013539.sql | grep '1,' | wc -l
30

I do not have 30 tables with zero rows, and none of my sequence next
values are 1 in production.

My pg_dump backups from before 9.1.1 were correct, now they are not:

diff backup_20111212031701.sql backup_20111223013539.sql | grep setval
| grep state_id

< SELECT pg_catalog.setval('cp_state_id_seq', 52, true);
> SELECT pg_catalog.setval('cp_state_id_seq', 1, false);

> In
> particular, is the ALTER ... SET DEFAULT command there, and exactly how
> does it spell the nextval() call?

It all looks correct to me, except for the incorrect setval() values.

--
Greg Donald