Dupes inserted that violate the PK

[kevin kempter <kevin@xxxxxxxxxxxxxxxxxxx>]

Hi All;

Sorry f this is a duplicate post, my  first post from yesterday never  
showed up.


we have a table that has a PK as follows:

CREATE TABLE btx (
   id integer NOT NULL,
   url character varying(256) NOT NULL,
   "password" character varying(50),
    btx_counter  integer,
...
   size double precision
);


CREATE SEQUENCE btx_id_seq
   INCREMENT BY 1
   NO MAXVALUE
   NO MINVALUE
   CACHE 1;


ALTER TABLE btx ALTER COLUMN id SET DEFAULT  
nextval('btx_id_seq'::regclass);



ALTER TABLE ONLY btx
   ADD CONSTRAINT btx_pk PRIMARY KEY (id);



We've run into a scenario where 2 inserted rows both got the same id  
value

# select id,url, btx_counter  from btx where url  = 'test1' and  
btx_counter = 449;
  id       |   url       | btx_counter
------------+------------+------------
194848 | test1     |        449
(1 row)

# select id,url,btx_counter from btx where url = 'test1' AND  
btx_counter = 450;
  id       |   url       | btx_counter
------------+------------+------------
194848 | test1     |        450
(1 row)

We suspected the index was corrupt but a reindex fails for obvious  
reasons.



Anyone have any Ideas why this would be possible ?  Any debugging  
advice or steps to verify that my db is not corrupted would be much  
appreciated..

/Kevin