Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Adrian Klaver
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 y

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Adrian Klaver
On 12/29/2011 08:13 AM, Greg Donald wrote: On Wed, Dec 28, 2011 at 4:58 PM, Adrian Klaver 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. Giv

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Merlin Moncure
On Thu, Dec 29, 2011 at 10:53 AM, Greg Donald wrote: > On Thu, Dec 29, 2011 at 10:27 AM, Merlin Moncure 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.  pr

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Greg Donald
On Thu, Dec 29, 2011 at 10:27 AM, Merlin Moncure 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

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Merlin Moncure
On Thu, Dec 29, 2011 at 10:20 AM, Greg Donald wrote: > On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane 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 >

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Greg Donald
On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane 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 p

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Greg Donald
On Wed, Dec 28, 2011 at 4:58 PM, Adrian Klaver 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-29 Thread Greg Donald
On Wed, Dec 28, 2011 at 4:57 PM, Tom Lane 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 n

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
On Wednesday, December 28, 2011 1:02:39 pm Greg Donald wrote: > On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane wrote: > > Greg Donald 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 tabl

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Tom Lane
Greg Donald 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, fals

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Greg Donald
On Wed, Dec 28, 2011 at 1:00 PM, Tom Lane wrote: > Greg Donald 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.

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Tom Lane
Adrian Klaver 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 r

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Tom Lane
Greg Donald 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 (e

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Merlin Moncure
On Wed, Dec 28, 2011 at 12:34 PM, Adrian Klaver wrote: > On Wed, Dec 28, 2011 at 8:38 AM, Merlin Moncure wrote: >> >> On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver >> wrote: >> >> > >> > I was thinking of the more generic case. The problem with 'new' in the >> > above bug >> > is that it is a

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
On Wed, Dec 28, 2011 at 8:38 AM, Merlin Moncure wrote: > On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver > 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_d

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Merlin Moncure
On Wed, Dec 28, 2011 at 10:20 AM, Adrian Klaver wrote: > On Wednesday, December 28, 2011 7:51:24 am Greg Donald wrote: >> On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver > wrote: >> > Wonder if it is related to this: >> > http://archives.postgresql.org/pgsql-bugs/2011-11/msg00098.php >> >> None of

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
On Wednesday, December 28, 2011 8:03:34 am Greg Donald wrote: > On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver 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

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
On Wednesday, December 28, 2011 7:51:24 am Greg Donald wrote: > On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver 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 thinki

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Greg Donald
On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver 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

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Greg Donald
On Wed, Dec 28, 2011 at 9:30 AM, Adrian Klaver 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/ > > I

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
On Wednesday, December 28, 2011 7:18:01 am Greg Donald wrote: > On Wed, Dec 28, 2011 at 9:07 AM, Adrian Klaver 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 column

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Greg Donald
On Wed, Dec 28, 2011 at 9:07 AM, Adrian Klaver 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 p

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Adrian Klaver
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

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Greg Donald
> 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 h

Re: [GENERAL] PostgreSQL 9.1 pg_dump setval() sets wrong value

2011-12-28 Thread Scott Marlowe
On Tue, Dec 27, 2011 at 10:19 PM, Greg Donald 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.s