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@Yahoo.com #