From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Subject: Re: Insert problem
Date: Mon, 10 Mar 2008 11:09:04 -0700
Message-ID: <20080310180919.EEA662E2DF5@postgresql.org> (raw)
In-Reply-To: <20080310162235.BB1422E024B@postgresql.org>
References: <20080310162235.BB1422E024B@postgresql.org>
At 09:20 AM 3/10/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Mon, 10 Mar 2008 00:14:12 +0000
>From: "Jamie Tufnell" <diesql@googlemail.com>
>To: pgsql-sql@postgresql.org
>Subject: Re: Insert problem
>Message-ID:
><b0a4f3350803091714n2b89425ev48410eb86451b973@mail.gmail.com>
>[snip]
> > table defination
> >
> > create sequence schItem_item_seq
> > create table schItem
> > (scid int NOT NULL references schedule ON DELETE CASCADE,
> > item int NOT NULL default nextval('schItem_item_seq'),
>
>[snip]
>
>It looks like there's already a row where scid=2072 and
>item=nextval('schItem_item_seq').
>
>Try:
>
>SELECT setval('schItem_item_seq', (SELECT max(item)+1 FROM schItem));
>
>And then run your query again.
>
>Cheers,
>J.
A friendly amendment to Jamie's (correct) advice. Be sure that no other
tools are obtaining id's from the sequence or inserting rows into
schItem when you run this.. (Safest way is to have the db offline when
running this). I got some good info from the experts here about this
while back and wrote up a little blog article detailing the issue (in
short it's a big pain to do it while the db is online, with no obvious
sure-fire solution):
http://www.misuse.org/science/2007/08/07/obtaining-a-block-of-ids-from-a-sequence-in-postgresql/
I hope this helps a little!
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: public@misuse.org
Subject: Re: Insert problem
In-Reply-To: <20080310180919.EEA662E2DF5@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox