Skip site navigation (1) Skip section navigation (2)

Dupes inserted that violate the PK

From: kevin kempter <kevin(at)kevinkempterllc(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Dupes inserted that violate the PK
Date: 2008-07-08 15:31:28
Message-ID: C7328F69-4C2A-49B1-80DC-7DC60C950E7B@kevinkempterllc.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

Responses

pgsql-admin by date

Next:From: Rex MabryDate: 2008-07-08 16:44:32
Subject: dropping a user in 8.2.6
Previous:From: Michael NacosDate: 2008-07-08 15:21:46
Subject: Re: How do i compare 2 postgresql instanses ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group