Обсуждение: PostgreSQL 9.1 pg_dump setval() sets wrong value

I upgraded to PostgreSQL 9.1.  I was using 8.4 previously.

My problem is with the new version of pg_dump.  It no longer
(consistently) dumps my sequence values correctly.


For example, I have a table

CREATE TABLE setting (
    id integer NOT NULL,
    company_id integer NOT NULL,
    [...]
);

The max(id) in that table is 61, and my sequence is dumped correctly,
I find this in my backup:

SELECT pg_catalog.setval('setting_id_seq', 61, true);


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


I know how to fix it, but I'm not sure why it's doing this?  Why are
some sequences set wrong while others are fine?

I Google'd and found Tom Lane's post from '06 about how 'sequences are
black boxes', but not much else turned up.

Surely I don't have to maintain a separate sequence fix-up script to
keep with my backups, do I?


Thanks.


--
Greg Donald

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

On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> < 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.

Those grep calls prove my old backups with 8.4 pg_dump were good to go
and now they are not with 9.1 pg_dump.

> I remain unclear as to what state is actually in the
> database, or what is being dumped,

The whole thing is being dumped.  One command /usr/bin/pg_dump cp,
that's it, nothing special.


--
Greg Donald

On Thu, Dec 29, 2011 at 10:20 AM, Greg Donald <gdonald@gmail.com> wrote:
> On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> < 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.
>
> Those grep calls prove my old backups with 8.4 pg_dump were good to go
> and now they are not with 9.1 pg_dump.
>
>> I remain unclear as to what state is actually in the
>> database, or what is being dumped,
>
> The whole thing is being dumped.  One command /usr/bin/pg_dump cp,
> that's it, nothing special.

if you take a bzipped schema only dump (pg_dump -s), I'd be happy to
look it over and eliminate the 'operator error' class of issues that
Tom is thinking might be happening.  private mail is ok.

merlin

On Thu, Dec 29, 2011 at 10:27 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> if you take a bzipped schema only dump (pg_dump -s), I'd be happy to
> look it over and eliminate the 'operator error' class of issues that
> Tom is thinking might be happening.  private mail is ok.

Operator error?  Wow.. so now I'm doing it wrong?

I'll pass, thanks for all your "help" guys.  It's been a blast.


--
Greg Donald

On Thu, Dec 29, 2011 at 10:53 AM, Greg Donald <gdonald@gmail.com> wrote:
> On Thu, Dec 29, 2011 at 10:27 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> if you take a bzipped schema only dump (pg_dump -s), I'd be happy to
>> look it over and eliminate the 'operator error' class of issues that
>> Tom is thinking might be happening.  private mail is ok.
>
> Operator error?  Wow.. so now I'm doing it wrong?
>
> I'll pass, thanks for all your "help" guys.  It's been a blast.

I did not suggest that.  I suggested they *might* be happening.  In
lieu of a reproducible test case (a couple of us took a stab at
creating one ourselves and could not come up with your issue) or some
other smoking gun it is something that has to ruled out.
Statistically speaking, operator error is by far the most common cause
of bug reports and as engineers we tend to rule out the most likely
culprits first.  It's nothing personal.

By the way, I've been diagnosing and fixing other people's problems on
this list for years and have a pretty good feel for what's a real
problem or not.  Your is one of those that can go either way and I was
willing to take time out of my day, gratis, to help you fix it on your
end and/or suggest a fix to the database itself.  Since you are
obviously unaware of the value of that help, not to mention Tom's
incredibly precious time, it is your curse to have to reset your
sequences for ever and ever.  Happy Holidays.

merlin

On 12/29/2011 08:13 AM, Greg Donald wrote:
> 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

One possible issue that I see is the lack of explicit options. Given
that you say you have two Postgres clusters on the machine it is
entirely possible that the above command is picking up ENVIRONMENT
variables that are pointing to a database different from the one you
think. Try the command with explicit options, i.e. -p some_port -d
database_name, that you know point to the database you want.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/29/2011 09:19 AM, Merlin Moncure wrote:

>>
>> I'll pass, thanks for all your "help" guys.  It's been a blast.
>
> I did not suggest that.  I suggested they *might* be happening.  In
> lieu of a reproducible test case (a couple of us took a stab at
> creating one ourselves and could not come up with your issue) or some
> other smoking gun it is something that has to ruled out.
> Statistically speaking, operator error is by far the most common cause
> of bug reports and as engineers we tend to rule out the most likely
> culprits first.  It's nothing personal.
>
> By the way, I've been diagnosing and fixing other people's problems on
> this list for years and have a pretty good feel for what's a real
> problem or not.  Your is one of those that can go either way and I was
> willing to take time out of my day, gratis, to help you fix it on your
> end and/or suggest a fix to the database itself.  Since you are
> obviously unaware of the value of that help, not to mention Tom's
> incredibly precious time, it is your curse to have to reset your
> sequences for ever and ever.  Happy Holidays.

To add. It is the difference between working the solution and working
the problem. The problem was you where seeing unexpected behavior. That
is given. The solution at this point is a work in progress and without
exploring all avenues will remain so. The bigger picture is that
potentially this is something that could affect more than you and if
that is the case, or not,  it would nice to nip it in the bud sooner
rather than later.

>
> merlin
>


--
Adrian Klaver
adrian.klaver@gmail.com