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.