Re: duplicate primary key entries?

Baldur Norddahl wrote:
> Hi,
>
> I just noticed something bad in our database:
>
> webshop=# select oid,* from content_loc where id=20488;
> oid | id | locale | name
> ---------+-------+--------+--------------
> 9781056 | 20488 | any | Rise Part II
> 9781058 | 20488 | any | Rise Part II
> (2 rows)
>
> webshop=# \d content_loc
> Table "public.content_loc"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer | not null
> locale | text | not null
> name | text |
> Indexes: content_loc_pkey primary key btree (id, locale)
> Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES content(id) ON UPDATE
> CASCADE ON DELETE CASCADE,
> $2 FOREIGN KEY (locale) REFERENCES languages(locale) ON
> UPDATE CASCADE ON DELETE CASCADE
>
>
>
> Apparently there are two rows with identical primary keys which should not be
> possible. Is this a know problem? Can I expect everything to be ok if I just
> delete the extra entry?

I would assume that btree index to be corrupt, otherwise it should have
led to a duplicate key error. So you have at least to reindex after
removing the extra entry.

The real question though is how did it get there? As far as I know it
allways turned out to be some damaged hardware (memory, disk) that led
to corrupted btree indexes.

Jan

>
> I am using the debian packages of postgresql 7.3.4-9.
>
> Thanks,
>
> Baldur
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #