Re: Duplicate keys inserted even with unique constraint.

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: Gam Er <gamero(dot)mails(at)gmail(dot)com>
Cc: PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Duplicate keys inserted even with unique constraint.
Date: 2018-08-15 18:01:26
Message-ID: CAH2-Wz=borqg3W9GraLuL0VnkzQPnnxrkwprot3r+UkyuE=HHg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, Aug 15, 2018 at 10:34 AM, Gam Er <gamero(dot)mails(at)gmail(dot)com> wrote:
> I have a table containing a key on two fields, among other indexes and other
> tables. So I can't insert values where both match an existing pair, that's
> my expectation. There is an index with the uniqueness constraint, that index
> was created from the start. All those key-pairs should be unique.
>
> Now i ran an UPDATE .. SET .. WHERE "table"."some_other_field" and that
> fails with an error message: duplicate key values value violates unique
> constraint (a, b).. already exists.
>
> I thought, that can't be possible, since I don't even touch the key
> attributes. I ran a SELECT and there really are duplicate keys in the table.
> How? Why?

It's probably some kind of data corruption. It's not impossible that
it's caused by a known bug, or even an unknown one.

Can you run amcheck on the affected index? It sounds like the
"heapallindexed" check [1] would be most likely to further isolate the
problem here, which is something that the v10 contrib/amcheck doesn't
have. You may wish to install the externally distributed amcheck from
the PGDG repo (sometimes called "amcheck_next"), in order to be able
to use this additional capability.

[1] https://www.postgresql.org/docs/devel/static/amcheck.html#id-1.11.7.11.8
--
Peter Geoghegan

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2018-08-16 00:18:07 Re: [PG_UPGRADE] 9.6 to 10.5
Previous Message Gam Er 2018-08-15 17:34:18 Duplicate keys inserted even with unique constraint.