pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot insert dup id in pk
6+ messages / 4 participants
[nested] [flat]
* Cannot insert dup id in pk
@ 2003-07-14 17:01 Scott Cain <cain@cshl.org>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Cain @ 2003-07-14 17:01 UTC (permalink / raw)
To: pgsql-sql
Hello,
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
happen, 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, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
^ permalink raw reply [nested|flat] 6+ messages in thread
* Cannot insert dup id in pk
@ 2003-07-15 13:00 Scott Cain <cain@cshl.org>
0 siblings, 2 replies; 6+ messages in thread
From: Scott Cain @ 2003-07-15 13:00 UTC (permalink / raw)
To: pgsql-sql
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, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot insert dup id in pk
@ 2003-07-15 14:31 Henshall, Stuart - TNP Southwest <shenshall@tnp-southwest.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Henshall, Stuart - TNP Southwest @ 2003-07-15 14:31 UTC (permalink / raw)
To: Scott Cain <cain@cshl.org>; pgsql-sql
I suspect the sequence is out of sync with the values actually in you
primary key (which I gues is fid.
Try this query
SELECT setval('public.fdata _fid_seq'::text,MAX(fid)+1) FROM fdata;
This should set the value of the sequence to the current maximum+1
hth,
- Stuart
P.S. Sorry about the format change the disclaimer adder forces
> -----Original Message-----
> From: Scott Cain [mailto:cain@cshl.org]
> Sent: 15 July 2003 14:00
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Cannot insert dup id in pk
>
>
> THIS EMAIL HAS BEEN SWEPT FOR VIRUSES BY THE NORTHCLIFFE
> GROUP MAILSWEEPER SERVER.
>
> 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,ftar
> get_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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
DISCLAIMER:The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot insert dup id in pk
@ 2003-07-15 14:43 Dmitry Tkach <dmitry@openratings.com>
parent: Scott Cain <cain@cshl.org>
1 sibling, 1 reply; 6+ messages in thread
From: Dmitry Tkach @ 2003-07-15 14:43 UTC (permalink / raw)
To: Scott Cain <cain@cshl.org>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot insert dup id in pk
@ 2003-07-15 15:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Cain <cain@cshl.org>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-07-15 15:25 UTC (permalink / raw)
To: Scott Cain <cain@cshl.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot insert dup id in pk
@ 2003-07-15 16:47 Scott Cain <cain@cshl.org>
parent: Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Cain @ 2003-07-15 16:47 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-07-15 16:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-14 17:01 Cannot insert dup id in pk Scott Cain <cain@cshl.org>
2003-07-15 13:00 Cannot insert dup id in pk Scott Cain <cain@cshl.org>
2003-07-15 14:43 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-15 16:47 ` Scott Cain <cain@cshl.org>
2003-07-15 15:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 14:31 Re: Cannot insert dup id in pk Henshall, Stuart - TNP Southwest <shenshall@tnp-southwest.co.uk>
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