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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

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