Re: Duplicate keys inserted even with unique constraint.

On Wed, Aug 15, 2018 at 10:34 AM, Gam Er <gamero.mails@gmail.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