Duplicate keys inserted even with unique constraint.

Hello pgsql-bugs,

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?

I can't find anything on the web. Most people just get that error message
when they try to INSERT duplicate values. I on the other hand really have
duplicate values and found them because UPDATE failed.

The table_size is around 59 GB, while the index_size is around 30 GB.
Multiple clients access and write at the same time.

Software versions: 9.4 -> 9.6 -> 10.4 (current) using Fedora 26, 27 and 28
(current)
So there was a database upgrade step from 9.x to 10.x.

It appears that postgres let's you store duplicate keys and only complains
later when you want to update one of those duplicated key rows, even when
you don't touch the key attributes.

That may be a bug, or I made some other mistake I didn't catch.

Threads and a database containing several GBs of data may be needed to
reproduce it.

The settings are the defaults for version 10.4, plus additional custom
settings, which were generated using the pgtune config generator with those
parameters:

# DB Version: 10# OS Type: linux# DB Type: oltp# Total Memory (RAM):
64 GB# CPUs num: 8# Data Storage: ssd

That's actually all I can say at the moment.

Regards.