On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Greg Donald <gdonald@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
