Thread: Dupes inserted that violate the PK

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

kevin kempter wrote:

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

What version is this?  We're investigating a similar problem on 8.1.11.



--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

8.2.9

I have a hunch what went wrong. We tested a warm standby failover
while the 'master' was still running - our warm standby includes a
process that grabs the final unarchived WAL segment from the master
pg_xlog directory. I have a hunch that this is where the disconnect
happened, I think the rule needs to be that we only execute a failover
based on bringing the master down as opposed to simply creating the
trigger file.  I'm trying to test this theory out now - I'll update
the list with my findings.



/Kevin


On Jul 8, 2008, at 10:52 AM, Alvaro Herrera wrote:

> kevin kempter wrote:
>
>> We suspected the index was corrupt but a reindex fails for obvious
>> reasons.
>
> What version is this?  We're investigating a similar problem on
> 8.1.11.
>
>
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support