Thread: insert bug

Hello,

I posted about this to the sql list, but on reflection, it feels a
little bit like a bug.  Either that, or some hole in my understanding of
the way the universe works.  Whatever it is, I wanted to ask the larger
group if there were any opinions on what I am seeing.

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

and the table definition:
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)) 

Note that I do not try to insert anything into fid, the primary key on
this table.  Why does Postgres think I am?

--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain@cshl.org
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory

On Tue, Jul 15, 2003 at 10:01:01AM -0400, Scott Cain wrote:

>not null default nextval('public.fdata _fid_seq'::text)
                          ^^^^^^^^^^^^^^^^^^^^^

Someone must have inserted some data beyond the range of that
sequence into your primary key.  When you do the nextval() on the
sequence, you get the next value, and it may clash with something
already inserted in fid.  A way to check is to select everything from
the sequence, and select the maximum value from your primary key; if
the latter is higher than the former, that's your problem.

You can use setval() to fix this.

It's probably useful to note that inserting something into a
serial field does not automatically increment the sequence
itself.  Indeed, the datatype "serial" is just a handy shorthand: it
creates the sequence for you, and sets the column to default
nextval() on the sequence.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110