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
>> 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,
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.
> 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
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
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);
> 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.
In response to
pgsql-general by date
|Next:||From: Tom Lane||Date: 2011-12-28 22:57:55|
|Subject: Re: PostgreSQL 9.1 pg_dump setval() sets wrong value |
|Previous:||From: karthikeyan||Date: 2011-12-28 19:29:40|
|Subject: Unable to Increase the column which was part of Primary key|