PostgreSQL 9.1 pg_dump setval() sets wrong value

On Tue, Dec 27, 2011 at 10:19 PM, Greg Donald <gdonald@gmail.com> wrote:

But then I have another table:

CREATE TABLE company (
   id integer NOT NULL,
   name character varying(64) NOT NULL,
   [...]
);

The max(id) in that table is 33, but my sequence is NOT dumped correctly:

SELECT pg_catalog.setval('company_id_seq', 1, false);

The max value of a sequence is NOT determined by the max value in the
table, but by what it was set to by being retrieved last. Are you
sure that the sequence is being used to insert those values into the
table?

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. When I use
pg_dump to backup the database most of the setval() calls in my SQL
file have a '1', like this:

SELECT pg_catalog.setval('company_id_seq', 1, false);

Some do not have a '1', some are actually set correctly.

Thanks.

--
Greg Donald

On Wednesday, December 28, 2011 6:54:54 am Greg Donald wrote:

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. When I use
pg_dump to backup the database most of the setval() calls in my SQL
file have a '1', like this:

SELECT pg_catalog.setval('company_id_seq', 1, false);

Some do not have a '1', some are actually set correctly.

So how are the sequences being tied to the tables? In your previous post the
table definitions did not show a SERIAL type or a default of nextval().

Thanks.

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Dec 28, 2011 at 9:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

So how are the sequences being tied to the tables? In your previous post the
table definitions did not show a SERIAL type or a default of nextval().

The id columns were created using "id serial NOT NULL".

I've gone back this morning and pulled older backups, from when I was
using pg_dump that came with my PostgreSQL 8.4 install. Those backups
look fine to me, all the setval() calls are there and correct. So it
looks like a problem with the new pg_dump best I can tell.

--
Greg Donald

On Wednesday, December 28, 2011 7:18:01 am Greg Donald wrote:

On Wed, Dec 28, 2011 at 9:07 AM, Adrian Klaver <adrian.klaver@gmail.com>

wrote:

So how are the sequences being tied to the tables? In your previous post
the table definitions did not show a SERIAL type or a default of
nextval().

The id columns were created using "id serial NOT NULL".

I've gone back this morning and pulled older backups, from when I was
using pg_dump that came with my PostgreSQL 8.4 install. Those backups
look fine to me, all the setval() calls are there and correct. So it
looks like a problem with the new pg_dump best I can tell.

Wonder if it is related to this:
http://archives.postgresql.org/pgsql-bugs/2011-11/msg00098.php

Might want to file bug report:
http://www.postgresql.org/support/submitbug/

In the meantime what about using 8.4 pg_dump to create dump file and then using
that file to load 9.1 server.

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

Wonder if it is related to this:
http://archives.postgresql.org/pgsql-bugs/2011-11/msg00098.php

None of my sequences are named 'new'. What do you mean?

Might want to file bug report:
http://www.postgresql.org/support/submitbug/

In the meantime what about using 8.4 pg_dump to create dump file and then using
that file to load 9.1 server.

That's an option. When I upgraded to PostgreSQL 9.1 on my Ubuntu
11.10 workstation, I noticed apt did not make me uninstall my old 8.4
version. Seemed happy to leave them both installed.

I found a number of sequence fixing options:

http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync

Right now I just have a long list of

ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name);

to fix things up for me on my local setup.

--
Greg Donald

On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

Might want to file bug report:
http://www.postgresql.org/support/submitbug/

Can't, not at the moment anyway.

psql --version

psql (PostgreSQL) 9.1.1

I'm not running the latest 9.1.2 version as required by that
particular bug report form.

I may try that version tonight, I actually found 9.1.2 packages

http://www.openscg.org/se/postgresql/packages.jsp

Maybe my fix will be in there.

--
Greg Donald

On Wednesday, December 28, 2011 7:51:24 am Greg Donald wrote:

On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver <adrian.klaver@gmail.com>

wrote:

Wonder if it is related to this:
http://archives.postgresql.org/pgsql-bugs/2011-11/msg00098.php

None of my sequences are named 'new'. What do you mean?

I was thinking of the more generic case. The problem with 'new' in the above bug
is that it is a reserved word and the list of reserved words recognized by
pg_dump changed in 9.0 or 9.1(?). If the table definitions you showed are correct
you have sequence columns named id. id is in the list of reserved words also:

http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html

--
Adrian Klaver
adrian.klaver@gmail.com

On Wednesday, December 28, 2011 8:03:34 am Greg Donald wrote:

On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver <adrian.klaver@gmail.com>

wrote:

Might want to file bug report:
http://www.postgresql.org/support/submitbug/

Can't, not at the moment anyway.

psql --version

psql (PostgreSQL) 9.1.1

I'm not running the latest 9.1.2 version as required by that
particular bug report form.

From the bug report:
Versions tested: 9.0.4 and 9.1.1
Installed from packages on Ubuntu

I may try that version tonight, I actually found 9.1.2 packages

http://www.openscg.org/se/postgresql/packages.jsp

Might want to hold off on that. As you noted earlier the Ubuntu install process
played nice with your previous 8.4 cluster. Using another package may not work
as well. I would check the Ubuntu repository first to see if there is not an
update already there.

Maybe my fix will be in there.

I don't see anything in the release notes that would indicate that.

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On Wednesday, December 28, 2011 7:51:24 am Greg Donald wrote:

On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver <adrian.klaver@gmail.com>

wrote:

Wonder if it is related to this:
http://archives.postgresql.org/pgsql-bugs/2011-11/msg00098.php

None of my sequences are named 'new'.  What do you mean?

I was thinking of the more generic case. The problem with 'new' in the above bug
is that it is a reserved word and the list of reserved words recognized by
pg_dump changed in 9.0 or 9.1(?). If the table definitions you showed are correct
you have sequence columns named id. id is in the list of reserved words also:

http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html

hm -- if sequences named 'id' are not dumping properly, I'd call that
a pretty significant bug.

merlin

On Wed, Dec 28, 2011 at 8:38 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:

I was thinking of the more generic case. The problem with 'new' in the

above bug

is that it is a reserved word and the list of reserved words recognized

by

pg_dump changed in 9.0 or 9.1(?). If the table definitions you showed

are correct

you have sequence columns named id. id is in the list of reserved words

also:

http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html

hm -- if sequences named 'id' are not dumping properly, I'd call that
a pretty significant bug.

Well just tested using 9.1.1 pg_dump to dump an 8.4.9 database with table:
CREATE TABLE id_test (
id integer NOT NULL,
fld_1 text
);

Everything worked including:

SELECT pg_catalog.setval('id_test_id_seq', 5, true)

which represents the correct sequence value.

So something else is going on.

merlin

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Dec 28, 2011 at 12:34 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On Wed, Dec 28, 2011 at 8:38 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:

I was thinking of the more generic case. The problem with 'new' in the
above bug
is that it is a reserved word and the list of reserved words recognized
by

pg_dump changed in 9.0 or 9.1(?). If the table definitions you showed

are correct
you have sequence columns named id. id is in the list of reserved words
also:

http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html

hm -- if sequences named 'id' are not dumping properly, I'd call that
a pretty significant bug.

Well just tested using 9.1.1 pg_dump to dump an 8.4.9 database with table:
CREATE TABLE id_test (
    id integer NOT NULL,
    fld_1 text
);

Everything worked including:

SELECT pg_catalog.setval('id_test_id_seq', 5, true)

which represents the correct sequence value.

So something else is going on.

yeah -- I was just about to report same. I also renamed the sequence
itself to 'id' with no issues. this was on 9.1.0.

merlin

Greg Donald <gdonald@gmail.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

Adrian Klaver <adrian.klaver@gmail.com> writes:

On Wednesday, December 28, 2011 7:51:24 am Greg Donald wrote:

None of my sequences are named 'new'. What do you mean?

I was thinking of the more generic case. The problem with 'new' in the above bug
is that it is a reserved word and the list of reserved words recognized by
pg_dump changed in 9.0 or 9.1(?). If the table definitions you showed are correct
you have sequence columns named id. id is in the list of reserved words also:

http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html

If that were the issue, Greg would be seeing pg_dump fail altogether,
not complete with bogus output. In any case, as that table says,
"id" is not a reserved word in Postgres.

regards, tom lane

On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Donald <gdonald@gmail.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

Greg Donald <gdonald@gmail.com> writes:

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

These "grep" calls are showing just exactly not enough to prove
anything. I remain unclear as to what state is actually in the
database, or what is being dumped, but I suspect at this point that you
may have multiple sequences of the same names in different schemas.

regards, tom lane

On Wednesday, December 28, 2011 1:02:39 pm Greg Donald wrote:

On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Donald <gdonald@gmail.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.

Actually it is showing the last_value. The CREATE SEQUENCE command pgAdmin shows
for the sequence uses that value as the START value for replicating the state of
the sequence.

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

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.

What is the pg_dump command, with options, you are using?

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.

--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

These "grep" calls are showing just exactly not enough to prove
anything.  I remain unclear as to what state is actually in the
database, or what is being dumped, but I suspect at this point that you
may have multiple sequences of the same names in different schemas.

I have one schema and one database on that one database server.

And like I said, it worked fine until 9.1. If it was any of those
reasons you suggest, would I not have experienced the same problem
back in 8.4? I would think so.

--
Greg Donald

On Wed, Dec 28, 2011 at 4:58 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

What is the pg_dump command, with options, you are using?

My backup shell script contains:

/usr/bin/pg_dump cp | bzip2 > $FILE

--
Greg Donald