Thread: Cannot insert dup id in pk
Hello,
I sent this question yesterday morning, but it was not allowed because I
wasn't subscribed to the list. If it did make it through, I appologize
for the dup.
I am having strange behavior when I try to do an insert. Postgres tells
me that it cannot insert a duplicate key into a primary key index, when
I am not trying to insert into it. Can anyone shed light on why this is
happening, or point out the error of my ways?
Here are the details:
wormbase=> \d fdata Table "public.fdata" Column | Type |
Modifiers
---------------+------------------------+--------------------------------------- -----------------fid |
integer | not null default nextval('public.fdata _fid_seq'::text)fref | character varying(100) |
notnull default ''fstart | integer | not null default '0'fstop | integer |
notnull default '0'fbin | double precision | not null default '0.000000'ftypeid | integer
| not null default '0'fscore | double precision |fstrand | character varying(3) |fphase
| character varying(3) |gid | integer | not null default '0'ftarget_start | integer
|ftarget_stop | integer |
Indexes: pk_fdata primary key btree (fid), fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
fdata_ftypeid_idx btree (ftypeid), fdata_gid_idx btree (gid)
Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f strand = '-'::character varying))
"chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase = '1'::character varying)) OR (fphase =
'2'::charactervarying))
Now a chunk from my query log:
Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key into unique index pk_fdata
Note that I do not try to insert anything into fid, the primary key on
this table. Why does Postgres think I am?
Thanks much,
Scott
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
<p><font size="2">I suspect the sequence is out of sync with the values actually in you primary key (which I gues is
fid.</font><br/><font size="2">Try this query</font><br /><font size="2">SELECT setval('public.fdata
_fid_seq'::text,MAX(fid)+1)FROM fdata;</font><br /><font size="2">This should set the value of the sequence to the
currentmaximum+1</font><br /><font size="2">hth,</font><br /><font size="2">- Stuart</font><br /><font size="2">P.S.
Sorryabout the format change the disclaimer adder forces</font><p><font size="2">> -----Original
Message-----</font><br/><font size="2">> From: Scott Cain [<a
href="mailto:cain@cshl.org">mailto:cain@cshl.org</a>]</font><br/><font size="2">> Sent: 15 July 2003 14:00</font><br
/><fontsize="2">> To: pgsql-sql@postgresql.org</font><br /><font size="2">> Subject: [SQL] Cannot insert dup id
inpk</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">> THIS EMAIL HAS
BEENSWEPT FOR VIRUSES BY THE NORTHCLIFFE </font><br /><font size="2">> GROUP MAILSWEEPER SERVER.</font><br /><font
size="2">></font><br /><font size="2">> Hello,</font><br /><font size="2">> </font><br /><font size="2">> I
sentthis question yesterday morning, but it was not </font><br /><font size="2">> allowed because I</font><br
/><fontsize="2">> wasn't subscribed to the list. If it did make it through, I </font><br /><font size="2">>
appologize</font><br/><font size="2">> for the dup.</font><br /><font size="2">> </font><br /><font size="2">>
Iam having strange behavior when I try to do an insert. </font><br /><font size="2">> Postgres tells</font><br
/><fontsize="2">> me that it cannot insert a duplicate key into a primary key </font><br /><font size="2">>
index,when</font><br /><font size="2">> I am not trying to insert into it. Can anyone shed light on </font><br
/><fontsize="2">> why this is</font><br /><font size="2">> happening, or point out the error of my
ways?</font><br/><font size="2">> </font><br /><font size="2">> Here are the details:</font><br /><font
size="2">>wormbase=> \d fdata</font><br /><font size="2">> Table
"public.fdata"</font><br/><font size="2">> Column | Type | </font><br
/><fontsize="2">> Modifiers </font><br /><font
size="2">> </font><br /><font size="2">>
---------------+------------------------+---------------------</font><br/><font size="2">> ------------------
-----------------</font><br/><font size="2">> fid | integer | not null default </font><br
/><fontsize="2">> nextval('public.fdata _fid_seq'::text)</font><br /><font size="2">> fref | character
varying(100)| not null default ''</font><br /><font size="2">> fstart | integer | not null
default'0'</font><br /><font size="2">> fstop | integer | not null default '0'</font><br
/><fontsize="2">> fbin | double precision | not null default '0.000000'</font><br /><font
size="2">> ftypeid | integer | not null default '0'</font><br /><font size="2">>
fscore | double precision |</font><br /><font size="2">> fstrand | character varying(3)
|</font><br/><font size="2">> fphase | character varying(3) |</font><br /><font size="2">>
gid | integer | not null default '0'</font><br /><font size="2">> ftarget_start |
integer |</font><br /><font size="2">> ftarget_stop | integer |</font><br /><font
size="2">>Indexes: pk_fdata primary key btree (fid),</font><br /><font size="2">> fdata_fref_idx btree
(fref,fbin, fstart, fstop, </font><br /><font size="2">> ftypeid, gid),</font><br /><font size="2">>
fdata_ftypeid_idxbtree (ftypeid),</font><br /><font size="2">> fdata_gid_idx btree (gid)</font><br /><font
size="2">>Check constraints: "chk_fdata_fstrand" ((fstrand = </font><br /><font size="2">> '+'::character
varying)OR (f strand = '-'::character varying))</font><br /><font size="2">> "chk_fdata_fphase"
(((fphase= </font><br /><font size="2">> '0'::character varying) OR (fp hase = '1'::character </font><br /><font
size="2">>varying)) OR (fphase = '2'::character varying))</font><br /><font size="2">> </font><br /><font
size="2">>Now a chunk from my query log:</font><br /><font size="2">> Jul 14 12:48:47 localhost postgres[2998]:
[107-1]LOG: </font><br /><font size="2">> query: INSERT INTO fdata </font><br /><font size="2">>
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftar</font><br/><font size="2">>
get_start,ftarget_stop)</font><br/><font size="2">> Jul 14 12:48:47 localhost postgres[2998]: [107-2] </font><br
/><fontsize="2">> VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'</font><br /><font size="2">>
12358',NULL,NULL)</font><br/><font size="2">> Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: </font><br
/><fontsize="2">> Cannot insert a duplicate key into unique index pk_fdata</font><br /><font size="2">>
</font><br/><font size="2">> Note that I do not try to insert anything into fid, the primary key on</font><br
/><fontsize="2">> this table. Why does Postgres think I am?</font><br /><font size="2">> </font><br /><font
size="2">>Thanks much,</font><br /><font size="2">> Scott</font><br /><font size="2">> </font><br /><font
size="2">>-- </font><br /><font size="2">>
--------------------------------------------------------------</font><br/><font size="2">> ----------</font><br
/><fontsize="2">> Scott Cain, Ph. D. </font><br /><font size="2">>
cain@cshl.org</font><br/><font size="2">> GMOD Coordinator (<a href="http://www.gmod.org/"
target="_blank">http://www.gmod.org/</a>) </font><br /><font size="2">> 216-392-3087</font><br
/><fontsize="2">> Cold Spring Harbor Laboratory</font><br /><font size="2">> </font><br /><font size="2">>
</font><br/><font size="2">> ---------------------------(end of </font><br /><font size="2">>
broadcast)---------------------------</font><br/><font size="2">> TIP 5: Have you checked our extensive
FAQ?</font><br/><font size="2">> </font><br /><font size="2">> <a
href="http://www.postgresql.org/docs/faqs/FAQ.html"
target="_blank">http://www.postgresql.org/docs/faqs/FAQ.html</a></font><br/><font size="2">> </font><br
/><p><b><i><fontsize="2">DISCLAIMER:</font></i><font size="2">The information in this message is confidential and may
belegally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised.
Ifyou are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or
omissiontaken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if
youhave received this message in error. Thank you.</font></b><br />
You must have your sequence out of date with the content of the table
(so that the next value in the sequence has already been inserted).
One way to get into a situation like that is loading the table data with
COPY (the input contains the pks, and the COPY command does not update
the sequence, you have to do that manually after the copy is done).
Another way is simply inserting a row with an explicitly specified pkey:
insert into fdata (fid,...) values (100, ...);
Now, assuming, that you current sequence value is less then 100, and
that the statement above succeedes (i.e., there is no fid=100 in the
table yet), you'll get your sequence out of date. You'll still be able
to use it, and insert the rows into the table *until* the current value
reaches 100 - once that happens, an attempt to insert with the default
fid will cause an error, because the sequence will generate a key, that
already exists.
To fix this, you need to do something like:
select setval ('fdata_fid_seq', (select fid from fdata order by fid
limit 1));
This will make sure that the next value your sequence generates is
greater than any key that already exists in the table.
I hope, it helps...
Dima
insert into fdata
Scott Cain wrote:
>Hello,
>
>I sent this question yesterday morning, but it was not allowed because I
>wasn't subscribed to the list. If it did make it through, I appologize
>for the dup.
>
>I am having strange behavior when I try to do an insert. Postgres tells
>me that it cannot insert a duplicate key into a primary key index, when
>I am not trying to insert into it. Can anyone shed light on why this is
>happening, or point out the error of my ways?
>
>Here are the details:
>wormbase=> \d fdata
> Table "public.fdata"
> Column | Type | Modifiers
>---------------+------------------------+--------------------------------------- -----------------
> fid | integer | not null default nextval('public.fdata _fid_seq'::text)
> fref | character varying(100) | not null default ''
> fstart | integer | not null default '0'
> fstop | integer | not null default '0'
> fbin | double precision | not null default '0.000000'
> ftypeid | integer | not null default '0'
> fscore | double precision |
> fstrand | character varying(3) |
> fphase | character varying(3) |
> gid | integer | not null default '0'
> ftarget_start | integer |
> ftarget_stop | integer |
>Indexes: pk_fdata primary key btree (fid),
> fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
> fdata_ftypeid_idx btree (ftypeid),
> fdata_gid_idx btree (gid)
>Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f strand = '-'::character varying))
> "chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase = '1'::character varying)) OR
(fphase= '2'::character varying))
>
>Now a chunk from my query log:
>Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
>Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
>Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key into unique index pk_fdata
>
>Note that I do not try to insert anything into fid, the primary key on
>this table. Why does Postgres think I am?
>
>Thanks much,
>Scott
>
>
>
Scott Cain <cain@cshl.org> writes:
> Note that I do not try to insert anything into fid, the primary key on
> this table. Why does Postgres think I am?
But you *are* trying to insert something into fid, namely the default
value:default nextval('public.fdata _fid_seq'::text)
My guess is that you have been inconsistent about whether you used the
sequence or explicit assignment to fid, and now you have some rows in
the table that have fid values higher than the current sequence value.
I'd suggest adjusting the sequence, along the lines of
SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;
regards, tom lane
On Tue, 2003-07-15 at 10:43, Dmitry Tkach wrote: > You must have your sequence out of date with the content of the table > (so that the next value in the sequence has already been inserted). > One way to get into a situation like that is loading the table data with > COPY (the input contains the pks, and the COPY command does not update > the sequence, you have to do that manually after the copy is done). Yes, this is exactly what happened. I had no idea that copy didn't update the sequence. I suspect I've got users who are being bitten by this and don't realize it. I'll have to change my "bulk loading" script to update the sequence after the load is done. Thanks much, Scott -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory